"Run-Time Error '1004'

[Fei]

Hi all,

When I run my VBA, there was a message:

"Run-Time Error '1004':

The pivotTable field name is not valid. To create a pivottable report,
you must use data that is organized as a list with labeled columns. If
you are changing the name of a pivottable field, you must type a new
name for the field."

It occurred when running:
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=PRange.Address)
Set PT = PTCache.CreatePivotTable(TableDestination:=shhet1.Range
("A1"), _
TableName:="PovitTable1")

Any help is appreciated

Fei

 

[Tim Williams]

Hard to tell without seeing the source data, but maybe try one of these...
Did you mean "sheet1" ?
Try just
SourceData:=PRange

Tim

 

[Fei]

Thanks all!

I have tried “create” - but not working.....

Sheet1 is the name of my worksheet, which contains my Pivot table –
“Dim Sheet1=Worksheets(Pivot Table)”. As the table and data are in two
worksheets, i have to specify the sheet name...

Also, after the run-time error 1004, when i continue to run the codes,
there is another error - "run-time error '-2147417848(80010108)":
Method 'CreatePivoteTable' of object 'PivotCache' failed'"....

I followed the sample codes, and the data is normal dataset......not
sure why...

Thanks

Fei