I am trying to create a pivot table after copying the data from one workbook to another workbook. Here is the code.
Dim ws As Worksheet
Set ws = Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"PIVOT_RANGE", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:=ws.Name & "!R1C1", TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion14
I am seeing different pivot table name every time. Sometimes it shows PivotTable1 and Sometimes it shows PivotTable2.
I am also getting the following error 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.
I actually have the data from A1:X and all the columns have column headings, but still I am getting this error message.
Just wanted to know is there a way to get pivot table name through VBA instead of giving the table name in code (i.e. TableName:= "PivotTable2") which is not the same most of the times.
Can someone please help me on this?
