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


.



.