My objective is to create several pivot tables using the same data source via vba.
Data is on worksheet "Data_sht" and the pivot table should display on the worksheet "Pivot_sht."
Note, the used range on "Data_sht" will change weekly (Number of rows and/or columns will change). Therefore, I plan to use another piece of code to dynamically change the data source range for every pivot table in the workbook.
The error that is received using the code below is "Object variable or With Block variable not set" and the line of code highlighted is
Have reviewed the code for over two hours and haven't determined the cause or resolution of the error.
Any insight as to a resolution of this error? Also, any additional thoughts as to the most efficient method to accomplish objective is appreciated.
Will continue to trouble shoot but thought that someone may be able to readily determine the solution...
Thanks in advance.
Data is on worksheet "Data_sht" and the pivot table should display on the worksheet "Pivot_sht."
Note, the used range on "Data_sht" will change weekly (Number of rows and/or columns will change). Therefore, I plan to use another piece of code to dynamically change the data source range for every pivot table in the workbook.
The error that is received using the code below is "Object variable or With Block variable not set" and the line of code highlighted is
Code:
Set PT = PTCache.CreatePivotTable(TableDestination:=Pivot_sht.Range("A2"), TableName:="PivotTable1")
Have reviewed the code for over two hours and haven't determined the cause or resolution of the error.
Any insight as to a resolution of this error? Also, any additional thoughts as to the most efficient method to accomplish objective is appreciated.
Will continue to trouble shoot but thought that someone may be able to readily determine the solution...
Thanks in advance.
Code:
Sub CreatePivot_Iter2()
Dim Data_sht As Worksheet
Dim Pivot_sht As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Dim FinalCol As Long
'Set Variables Equal to Data Sheet and Pivot Sheet
Set Data_sht = ThisWorkbook.Worksheets("Data")
Set Pivot_sht = ThisWorkbook.Worksheets("Pivot Table")
' Delete any prior pivot tables
For Each PT In Pivot_sht.PivotTables
PT.TableRange2.Clear
Next PT
' Define input area and set up a Pivot Cache
FinalRow = Data_sht.Cells(65536, 1).End(xlUp).Row
FinalCol = Data_sht.Cells(1, Application.Columns.Count).End(xlToLeft).Column
Set PRange = Data_sht.Cells(1, 1).Resize(FinalRow, FinalCol) 'original line
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange.Address) 'Iter1
Set PT = PTCache.CreatePivotTable(TableDestination:=Pivot_sht.Range("A2"), TableName:="PivotTable1")
PT.ManualUpdate = True
' Set up the row & column fields
PT.AddFields RowFields:=Array("Product", "Customer"), ColumnFields:="Region"
' Set up the data fields
With PT.PivotFields("Revenue")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With
' Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
' Added for the project files
Pivot_sht.Select
End Sub