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