I am getting fancy, this is the first time I have tried to create a pivot table via a Macro, I am following my Mr Excel VBA and Macro book, I am now stuck. Below I have a section of the code refering to PT.Manualupdate = True, when running the macro I get a bug stating what you see in the title of the message. Can anyone point me in the right direction, the code is below.
'Creating Pivot Table
Sub Create_Pivot_Table()
Dim WSD As Worksheet
Dim AP As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Set WSD = Worksheets("Pivot_Table")
Set AP = Worksheets("AP_Parking_Lot")
'Delete any Prior Pivot Tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
'Define Input area and set up a Pivot Cache
FinalRow = AP.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = AP.Cells(1, Application.Columns.Count).End(xlToLeft).Column
Set PRange = AP.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange.Address)
'Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Cells(2, FinalCol + 2), TableName:="Pivot_Table")
'Turn off updating while building the table
PT.ManualUpdate = True
'set up the row & Column fields
PT.AddFields RowFields:=Array("Client", "Prof. Center", "Vendor", "PO Number", "Aging Days", "Document Date", "Posting Date", "Reference", "Text", "Document Number", "Invoice Number", "WBS", "GL ACCT"), PageFields:=Array("Finance Director", "Capital YorN", "OOS YorN", "Goods Receipt YorN"), AddToTable:="Inv. Amount"
'set up the data fields
With PT.PivotFields("Inv. Amount")
.Orentation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
.Name = "Accrual"
End With
'Calc the pivot Table
PT.ManualUpdate = False
'Format Pivot Table
PT.ShowTableStyleRowStripes = True
PT.TableSyle2 = "PivotstyleMedium10"
With PT
.ColumnGrand = False
.RowGrand = False
.RepeatAllLabels xlRepeatLabels
End With
WSD.Activate
'Creating Pivot Table
Sub Create_Pivot_Table()
Dim WSD As Worksheet
Dim AP As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Set WSD = Worksheets("Pivot_Table")
Set AP = Worksheets("AP_Parking_Lot")
'Delete any Prior Pivot Tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
'Define Input area and set up a Pivot Cache
FinalRow = AP.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = AP.Cells(1, Application.Columns.Count).End(xlToLeft).Column
Set PRange = AP.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange.Address)
'Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Cells(2, FinalCol + 2), TableName:="Pivot_Table")
'Turn off updating while building the table
PT.ManualUpdate = True
'set up the row & Column fields
PT.AddFields RowFields:=Array("Client", "Prof. Center", "Vendor", "PO Number", "Aging Days", "Document Date", "Posting Date", "Reference", "Text", "Document Number", "Invoice Number", "WBS", "GL ACCT"), PageFields:=Array("Finance Director", "Capital YorN", "OOS YorN", "Goods Receipt YorN"), AddToTable:="Inv. Amount"
'set up the data fields
With PT.PivotFields("Inv. Amount")
.Orentation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
.Name = "Accrual"
End With
'Calc the pivot Table
PT.ManualUpdate = False
'Format Pivot Table
PT.ShowTableStyleRowStripes = True
PT.TableSyle2 = "PivotstyleMedium10"
With PT
.ColumnGrand = False
.RowGrand = False
.RepeatAllLabels xlRepeatLabels
End With
WSD.Activate