Error when creating Pivot table from macro

[tinge89]

Hi guys,

I'm getting the run-time error "1004" when i run the macro.

It is saying that the PivotTable field name is not validl; when I go to debug it, the following is highlighted in the VBA.

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R3C2:R96C8", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion12

Any ideas?

Thanks

[RHCPgergo]

Hi,

I think you can't use PivotTable1 as a name, because it is either a reserved name in Excel or there's already another pivot table with the same name.

edit: I may be wrong though, just an idea.

[tinge89]

Thanks for your response, I've tried changing the name of the table but it is still coming up with the same error.

[RHCPgergo]

Maybe these links can help?

http://www.excelforum.com/excel-gene...e-invalid.html
> Blank cell for PivotField name (on top of source data column)
or
> Hidden column (cell? row?) in source data

OR

http://support.microsoft.com/kb/141286
"This problem occurs when the first row of the range from which the PivotTable is attempting to pull data contains one or more empty cells. "