VBA Error
 

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:=xlDatab ase, _
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:=xlDatab ase,
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

VBA Error
 

I forgot to add I'm using Excel 2000...
Thanks,
chris

VBA Error
 

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" 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:=xlDatab ase, _
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:=xlDatab ase,
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

VBA Error
 

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:=xlDatab ase,
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" 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:=xlDatab ase, _
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:=xlDatab ase,
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


.

VBA Error
 

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:=xlDatab ase, _
SourceData:=MySourceData).CreatePivotTable

Hth
Regards
Pascal

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:=xlDatab ase,
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" 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:=xlDatab ase, _
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:=xlDatab ase,
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


.

VBA Error
 

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:=xlData base, _
SourceData:=MySourceData).CreatePivotTable

Hth
Regards
Pascal

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:=xlDatab ase,
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" 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:=xlDatab ase,
_
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:=xlDatab ase,
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


.



.

VBA Error
 

Make sure arguments follow correctly on one unique line

Regards
Pascal

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:=xlData base, _
SourceData:=MySourceData).CreatePivotTable

Hth
Regards
Pascal

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:=xlDatab ase,
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" 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:=xlDatab ase,
_
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:=xlDatab ase,
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


.



.

VBA Error
 

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

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:=xlDat abase, _
SourceData:=MySourceData).CreatePivotTable

Hth
Regards
Pascal

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:=xlDatab ase,
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" 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:=xlDatab ase,
_
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:=xlData base,
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


.



.



.