VBA Error

C

ChrisBatt

Hi,

I have a piece of code that I wrote a while back to put
data from a specific spreadsheet into a pivot table. Now
that I have learnt more about VBA, I wanted to go in and
make some changes, so that the code was a little more
efficient. Here is the original code:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:= "ALL_BW!R1C1:R65000C3").CreatePivotTable
TableDestination:="", TableName:= "PivotTable1"
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

This worked fine, except for the fact that I consistently
had less than 65,536 lines of data, and so would get a
"Blanks" line on my pivot table. I tried this piece of
code to eliminate that problem:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=Range([all_bw!a1],
[all_bw!a65536].End(xlUp)).Resize(, 3)).CreatePivotTable
TableDestination:="", TableName:="PivotTable1"
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

And I get a "Method 'Add' of Object 'PivotCaches' failed".
I have tried looking on various websites, books that I
have, etc., but all to no avail.
Does anybody see what the problem is, or am I going
crazy???
Thanks so much for your help.
Chris Battiston
 
P

papou

Hi Chris
Not too sure but as it looks it seems SourceData is expecting a R1C1 style
reference
This would explain the error you keep getting?
Worth trying?

HTH
Regards
Pascal
 
G

Guest

Well, that got rid of 1/2 my error message - now it's just
saying that an object is required...
I put in (the three *'s are to emphasise the change, they
are not part of my code....):

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=Range***([all_bw!r1c1],
[all_bw!r65536c1]***.End(xlUp)).Resize(, 3)).CreatePivotTable
TableDestination:="", TableName:="PivotTable1"
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
Click to expand...

-----Original Message-----
Hi Chris
Not too sure but as it looks it seems SourceData is expecting a R1C1 style
reference
This would explain the error you keep getting?
Worth trying?

HTH
Regards
Pascal

"ChrisBatt" <[email protected]> a écrit dans le message de
Hi,

I have a piece of code that I wrote a while back to put
data from a specific spreadsheet into a pivot table. Now
that I have learnt more about VBA, I wanted to go in and
make some changes, so that the code was a little more
efficient. Here is the original code:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:= "ALL_BW!R1C1:R65000C3").CreatePivotTable
TableDestination:="", TableName:= "PivotTable1"
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

This worked fine, except for the fact that I consistently
had less than 65,536 lines of data, and so would get a
"Blanks" line on my pivot table. I tried this piece of
code to eliminate that problem:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=Range([all_bw!a1],
[all_bw!a65536].End(xlUp)).Resize(, 3)).CreatePivotTable
TableDestination:="", TableName:="PivotTable1"
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

And I get a "Method 'Add' of Object 'PivotCaches' failed".
I have tried looking on various websites, books that I
have, etc., but all to no avail.
Does anybody see what the problem is, or am I going
crazy???
Thanks so much for your help.
Chris Battiston
Click to expand...


.
Click to expand...
 
P

papou

Dim LastR As Long
LastR = Sheets("ALL_BW").Range("A65536").End(xlUp).Row
Dim MySourceData As String
MySourceData = "ALL_BW!R1C1:R" & LastR & "C3"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=MySourceData).CreatePivotTable

Hth
Regards
Pascal

<[email protected]> a écrit dans le message de
Well, that got rid of 1/2 my error message - now it's just
saying that an object is required...
I put in (the three *'s are to emphasise the change, they
are not part of my code....):

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=Range***([all_bw!r1c1],
[all_bw!r65536c1]***.End(xlUp)).Resize(, 3)).CreatePivotTable
TableDestination:="", TableName:="PivotTable1"
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
Click to expand...

-----Original Message-----
Hi Chris
Not too sure but as it looks it seems SourceData is expecting a R1C1 style
reference
This would explain the error you keep getting?
Worth trying?

HTH
Regards
Pascal

"ChrisBatt" <[email protected]> a écrit dans le message de
Hi,

I have a piece of code that I wrote a while back to put
data from a specific spreadsheet into a pivot table. Now
that I have learnt more about VBA, I wanted to go in and
make some changes, so that the code was a little more
efficient. Here is the original code:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:= "ALL_BW!R1C1:R65000C3").CreatePivotTable
TableDestination:="", TableName:= "PivotTable1"
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

This worked fine, except for the fact that I consistently
had less than 65,536 lines of data, and so would get a
"Blanks" line on my pivot table. I tried this piece of
code to eliminate that problem:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=Range([all_bw!a1],
[all_bw!a65536].End(xlUp)).Resize(, 3)).CreatePivotTable
TableDestination:="", TableName:="PivotTable1"
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

And I get a "Method 'Add' of Object 'PivotCaches' failed".
I have tried looking on various websites, books that I
have, etc., but all to no avail.
Does anybody see what the problem is, or am I going
crazy???
Thanks so much for your help.
Chris Battiston
Click to expand...


.
Click to expand...
 
G

Guest

Thank you very much for writing that for me - BUT...
I now get an "Arguement not optional" error...
I had tried testing it using Macro99 as the name, and when
I try running it, the Sub Macro99() goes yellow, and the
..CreatePivotTable goes highlighted....
Would it be wrong to throw my computer out the window? :)
-----Original Message-----
Dim LastR As Long
LastR = Sheets("ALL_BW").Range("A65536").End(xlUp).Row
Dim MySourceData As String
MySourceData = "ALL_BW!R1C1:R" & LastR & "C3"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=MySourceData).CreatePivotTable

Hth
Regards
Pascal

<[email protected]> a écrit dans le message de
Well, that got rid of 1/2 my error message - now it's just
saying that an object is required...
I put in (the three *'s are to emphasise the change, they
are not part of my code....):

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=Range***([all_bw!r1c1],
[all_bw!r65536c1]***.End(xlUp)).Resize(, 3)).CreatePivotTable
TableDestination:="", TableName:="PivotTable1"
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
Click to expand...

-----Original Message-----
Hi Chris
Not too sure but as it looks it seems SourceData is expecting a R1C1 style
reference
This would explain the error you keep getting?
Worth trying?

HTH
Regards
Pascal

"ChrisBatt" <[email protected]> a écrit dans le message de
Hi,

I have a piece of code that I wrote a while back to put
data from a specific spreadsheet into a pivot table. Now
that I have learnt more about VBA, I wanted to go in and
make some changes, so that the code was a little more
efficient. Here is the original code:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:= "ALL_BW!R1C1:R65000C3").CreatePivotTable
TableDestination:="", TableName:= "PivotTable1"
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

This worked fine, except for the fact that I consistently
had less than 65,536 lines of data, and so would get a
"Blanks" line on my pivot table. I tried this piece of
code to eliminate that problem:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=Range([all_bw!a1],
[all_bw!a65536].End(xlUp)).Resize(, 3)).CreatePivotTable
TableDestination:="", TableName:="PivotTable1"
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

And I get a "Method 'Add' of Object 'PivotCaches' failed".
I have tried looking on various websites, books that I
have, etc., but all to no avail.
Does anybody see what the problem is, or am I going
crazy???
Thanks so much for your help.
Chris Battiston
Click to expand...


.
Click to expand...


.
Click to expand...
 
P

papou

Make sure arguments follow correctly on one unique line

Regards
Pascal

<[email protected]> a écrit dans le message de
Thank you very much for writing that for me - BUT...
I now get an "Arguement not optional" error...
I had tried testing it using Macro99 as the name, and when
I try running it, the Sub Macro99() goes yellow, and the
..CreatePivotTable goes highlighted....
Would it be wrong to throw my computer out the window? :)
-----Original Message-----
Dim LastR As Long
LastR = Sheets("ALL_BW").Range("A65536").End(xlUp).Row
Dim MySourceData As String
MySourceData = "ALL_BW!R1C1:R" & LastR & "C3"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=MySourceData).CreatePivotTable

Hth
Regards
Pascal

<[email protected]> a écrit dans le message de
Well, that got rid of 1/2 my error message - now it's just
saying that an object is required...
I put in (the three *'s are to emphasise the change, they
are not part of my code....):

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=Range***([all_bw!r1c1],
[all_bw!r65536c1]***.End(xlUp)).Resize(, 3)).CreatePivotTable
TableDestination:="", TableName:="PivotTable1"
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
Click to expand...

-----Original Message-----
Hi Chris
Not too sure but as it looks it seems SourceData is expecting a R1C1 style
reference
This would explain the error you keep getting?
Worth trying?

HTH
Regards
Pascal

"ChrisBatt" <[email protected]> a écrit dans le message de
Hi,

I have a piece of code that I wrote a while back to put
data from a specific spreadsheet into a pivot table. Now
that I have learnt more about VBA, I wanted to go in and
make some changes, so that the code was a little more
efficient. Here is the original code:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:= "ALL_BW!R1C1:R65000C3").CreatePivotTable
TableDestination:="", TableName:= "PivotTable1"
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

This worked fine, except for the fact that I consistently
had less than 65,536 lines of data, and so would get a
"Blanks" line on my pivot table. I tried this piece of
code to eliminate that problem:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=Range([all_bw!a1],
[all_bw!a65536].End(xlUp)).Resize(, 3)).CreatePivotTable
TableDestination:="", TableName:="PivotTable1"
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

And I get a "Method 'Add' of Object 'PivotCaches' failed".
I have tried looking on various websites, books that I
have, etc., but all to no avail.
Does anybody see what the problem is, or am I going
crazy???
Thanks so much for your help.
Chris Battiston
Click to expand...


.
Click to expand...


.
Click to expand...
 
G

Guest

Got it - for some reason it required the
TableDestination:="", TableName:="PivotTable1"
arguements....Thank you so very, very much for all your
time and patience...
-----Original Message-----
Make sure arguments follow correctly on one unique line

Regards
Pascal

<[email protected]> a écrit dans le message de
Thank you very much for writing that for me - BUT...
I now get an "Arguement not optional" error...
I had tried testing it using Macro99 as the name, and when
I try running it, the Sub Macro99() goes yellow, and the
..CreatePivotTable goes highlighted....
Would it be wrong to throw my computer out the window? :)
-----Original Message-----
Dim LastR As Long
LastR = Sheets("ALL_BW").Range("A65536").End(xlUp).Row
Dim MySourceData As String
MySourceData = "ALL_BW!R1C1:R" & LastR & "C3"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=MySourceData).CreatePivotTable

Hth
Regards
Pascal

<[email protected]> a écrit dans le message de
Well, that got rid of 1/2 my error message - now it's just
saying that an object is required...
I put in (the three *'s are to emphasise the change, they
are not part of my code....):

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=Range***([all_bw!r1c1],
[all_bw!r65536c1]***.End(xlUp)).Resize(, 3)).CreatePivotTable
TableDestination:="", TableName:="PivotTable1"
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

-----Original Message-----
Hi Chris
Not too sure but as it looks it seems SourceData is expecting a R1C1 style
reference
This would explain the error you keep getting?
Worth trying?

HTH
Regards
Pascal

"ChrisBatt" <[email protected]> a
Click to expand...
écrit
dans le message de
Hi,

I have a piece of code that I wrote a while back to put
data from a specific spreadsheet into a pivot table. Now
that I have learnt more about VBA, I wanted to go in and
make some changes, so that the code was a little more
efficient. Here is the original code:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:= "ALL_BW!R1C1:R65000C3").CreatePivotTable
TableDestination:="", TableName:= "PivotTable1"
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

This worked fine, except for the fact that I consistently
had less than 65,536 lines of data, and so would get a
"Blanks" line on my pivot table. I tried this piece of
code to eliminate that problem:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=Range([all_bw!a1],
[all_bw!a65536].End(xlUp)).Resize(, 3)).CreatePivotTable
TableDestination:="", TableName:="PivotTable1"
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

And I get a "Method 'Add' of Object 'PivotCaches' failed".
I have tried looking on various websites, books that I
have, etc., but all to no avail.
Does anybody see what the problem is, or am I going
crazy???
Thanks so much for your help.
Chris Battiston


.
Click to expand...


.
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pivot Table Macro with dynamic source data 1
Format Pivot total rows 2
Pivot table Macro help!!! 3
Avoiding an extra sheet when creating a pivot table 3
Please Help! Need to add a Variable Range in my Excel Macro withPivot Tables 3
PivotTable Macro not working 2
VBA for pivot tables 2
Invalid Procedure call or argument 1

Top