Pivot table issue

[Marcin]

Hi All

I'm having trouble with generating a pivot tablel through VBA. I recorded a macro and changed only the SourceData. The code crushes and does nothing. I tried to run the macro I recorded and to my suprise the code crushed as well, resulting with the same error (Invalid procedure argument). Could any one explain to me why is this code crushing. Below I place a sample code:

Set c = sheet.Range("A1", "G10")
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
c, Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="BCS Finance Data!R1C9", TableName:= _
"PivotTable3", DefaultVersion:=xlPivotTableVersion12
'Sheets("BCS Finance Data").Select
sheet.cells(1, 9).Select
sheet.Range("J9").Select
With sheet.PivotTables("PivotTable3")
.InGridDropZones = True
.RowAxisLayout xlTabularRow
End With
With sheet.PivotTables("PivotTable3").PivotFields("PLs")
.Orientation = xlRowField
.Position = 1
End With

[Bob Phillips]

Your source data is just 2 cells? That doesn't seem right.

[Marcin]

I'm not an experined VBA user however it does not make any difference if I write: Range("A1:G10") or Range("A1","G10"). In both cases I have 70 cells.

I noticed that when I use the presented code but place the Pivot table in a new sheet then it works fine. But I need to put in the same worksheet as my source data

[Bob Phillips]

Aah, you are right. I was reading it as Range("A1,G10") which is just 2 cells, but Range("A1","G10") is the same as Range("A1:G10"), which is 70 cells.