My code below is my attempt at opening a workbook, then create a pivot table based on a data range on a tab titled "data".
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTBookY As PivotTable
Dim PRange As Range
Dim lastRow As Long
Dim LastCol As Long
Application.ScreenUpdating = False
Set UKBook = _
Workbooks.Open _
("File Path")
Worksheets("Data").Visible = True
Sheets.Add
ActiveSheet.Name = "B22"
Set PSheet = ActiveWorkbook.Worksheets("B22")
Set DSheet = ActiveWorkbook.Worksheets("Data")
'Define Data Range
lastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(lastRow, LastCol)
'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange.Address)
'Insert Blank Pivot Table
Set PTBookY = PSheet.PivotTables.Add(PivotCache:=PCache, TableDestination:=PSheet.Range("A1"), TableName:="PTBookY")
I have additional code past this to enter rows/columns/values for the pivot table, but don't believe it is relevant. Please let me know if more is needed.
However, I am inconsistently receiving "Run-time error 1004: The PivotTable field name is not valid". Sometimes the code will go through, and other times it will get stuck at inserting the blank pivot table.
I have noticed the code will go through more consistently if already have the workbook open, and am on the Data tab, but this may be anecdotal.
The data range has headers in every column, and the file path and sheet names are correct.
Would appreciate anyone who could point me in the right direction.