Object Variable With Block Variable Not set - Error

CVBASIMON

New Member
Joined
May 30, 2012
Messages
30
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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

CVBASIMON

New Member
Joined
May 30, 2012
Messages
30
I have now changed the Macro around, it runs through the data however the tab is blank...any suggestions. New code 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
Sheets("AP_Parking_Lot").Select
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
Sheets("Pivot_Table").Select
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Cells(2, FinalCol + 2), TableName:="PivotTable1")
'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")
'set up the data fields
With PT.PivotFields("Inv. Amount")
.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
End With
'Calc the pivot Table
PT.ManualUpdate = False
PT.ManualUpdate = True
'Format Pivot Table
PT.ShowTableStyleRowStripes = True
With PT
.ColumnGrand = False
.RowGrand = False
.RepeatAllLabels xlRepeatLabels
End With
WSD.Activate
Range("A2").Select


Application.EnableEvents = True

Application.ScreenUpdating = True
 

Watch MrExcel Video

Forum statistics

Threads
1,123,177
Messages
5,600,160
Members
414,367
Latest member
dw970906

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top