Object Required- Error Message with VBA Pivot table

M

MHamid

Active Member
Joined
Jan 31, 2013
Messages
472
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello,

I am getting the following error message when attempting to run my Pivot table code. "Object Required"

What am I doing wrong?

Full code:
Code: 
Sub FilterRatedAuditsImpactingOT()
'
' Rated Audits Impacting O&T Chart Macro
' Created by Miriam Hamid
' Created on 5/11/2018
'Declare Variables to hold source and destination cell range address
Dim mySourceData As String
Dim myDestinationRange As String
Dim SourceDataAddress As String
'Declare Variables to hold references to source and destination worksheets and Pivot Table
Dim SourceSheet As Worksheet
Dim DestSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As String
'Declare Variales to hold row and column numbers that will define source data cell range
Dim FirstRow As Long
Dim LastRow As Long
Dim FirstCol As Long
Dim LastCol As Long
'Set/Define Source and Destination Variables
With ThisWorkbook
    Set SourceSheet = .Worksheets("Audit_Plan")
    Set DestSheet = .Worksheets("OTRC MOR File")
End With
'Obtain address of destination cell range
PRange = DestSheet.Range("A6").Address(ReferenceStyle:=xlR1C1)
'identify first row and first column of source data cell range
FirstRow = 6
FirstCol = 1
'Find last row and last column of source data cell range
'And ontain address of source data cell range
With SourceSheet.Cells
    LastRow = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    LastCol = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    mySourceData = .Range(.Cells(FirstRow, FirstCol), .Cells(LastRow, LastCol)).Address(ReferenceStyle:=xlR1C1)
End With
'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(3, 43), _
TableName:="PivotTable4", DefaultVersion:=6)
    
'Insert Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(3, 43), TableName:="PivotTable4")
    
    
'    Sheets("Audit_Plan").Select
'    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Audit_Plan!R6C1:R1048576C93", Version:=6).CreatePivotTable tabledestination _
        :="OTRC MOR File!R3C43", TableName:="PivotTable4", DefaultVersion:=6
'    Sheets("OTRC MOR File").Select
'    Cells(3, 43).Select
    
'Insert Pivot Data
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("QTR")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("Audit_Number"), "Count of Audit_Number", xlCount
    ActiveWindow.SmallScroll ToRight:=6
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Audit_Status")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Control_Rating")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("L1_Area")
        .Orientation = xlRowField
        .Position = 4
    End With
    Range("AQ3").Select
    ActiveSheet.PivotTables("PivotTable4").RowAxisLayout xlTabularRow
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("QTR")
        .PivotItems("(blank)").Visible = False
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Control_Rating")
        .PivotItems("Not Rated").Visible = False
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("L1_Area")
        .PivotItems("Business").Visible = False
    End With
    Range("AU3").Select
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Count of Audit_Number"). _
        Caption = "Count"
    Columns("AQ:AU").EntireColumn.AutoFit
    Range("AJ6").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q1 (Jan - Mar)"",""Audit_Status"",""Completed"")"
    Range("AJ7").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q1 (Jan - Mar)"",""Audit_Status"",""In Progress"")"
    Range("AJ8").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q1 (Jan - Mar)"",""Audit_Status"",""Not Started"")"
    Range("AK6").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q2 (Apr - Jun)"",""Audit_Status"",""Completed"")"
    Range("AK7").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q2 (Apr - Jun)"",""Audit_Status"",""In Progress"")"
    Range("AK8").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q2 (Apr - Jun)"",""Audit_Status"",""Not Started"")"
    Range("AL7").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q3 (Jul - Sep)"",""Audit_Status"",""In Progress"")"
    Range("AL8").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q3 (Jul - Sep)"",""Audit_Status"",""Not Started"")"
    Range("AL6").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q3 (Jul - Sep)"",""Audit_Status"",""Completed"")"
    Range("AM7").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q4 (Oct - Dec)"",""Audit_Status"",""In Progress"")"
    Range("AM8").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q4 (Oct - Dec)"",""Audit_Status"",""Not Started"")"
    Range("AM6").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q4 (Oct - Dec)"",""Audit_Status"",""Completed"")"
    ActiveWindow.SmallScroll Down:=-3
    ActiveWindow.SmallScroll ToRight:=-2
End Sub

This is the section of the code where the error message appears:
Code: 
'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(3, 43), _
TableName:="PivotTable4", DefaultVersion:=6)


Thank you
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Why use SET?
My equivalent line from an in use macro that creates a Pivot Table is:
Code: 
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "ElavonData", Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:="ePivot!R1C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion15
 
Upvote 0
PSheet isn't declared or set to reference anything, anywhere in the posted code.

What is it meant to refer to?
 
Upvote 0
Hello,

PSheet is the destination sheet. I completely forgot that I switched it from PSheet to DestSheet. I updated the code to replace PSheet with DestSheet.

I was using this site to help me create the code (https://powerspreadsheets.com/vba-create-pivot-table/)
I thought I had to use the SET because if you see in the next part of the code when I want to insert the pivot table I use PCache to do that.

However, I see that it was not truly needed. Is there a better way to condense the Pivot Data section at the end of the code?
Code: 
Sub FilterRatedAuditsImpactingOT()
'
' Rated Audits Impacting O&T Chart Macro
' Created by Miriam Hamid
' Created on 5/11/2018
'Declare Variables to hold source cell range address
Dim mySourceData As String
'Declare Variables to hold references to source and destination worksheets and Pivot Table
Dim SourceSheet As Worksheet
Dim DestSheet As Worksheet
'Declare Variales to hold row and column numbers that will define source data cell range
Dim FirstRow As Long
Dim LastRow As Long
Dim FirstCol As Long
Dim LastCol As Long
'Set/Define Source and Destination Variables
With ThisWorkbook
    Set SourceSheet = .Worksheets("Audit_Plan")
    Set DestSheet = .Worksheets("OTRC MOR File")
End With
'identify first row and first column of source data cell range
FirstRow = 6
FirstCol = 1
'Find last row and last column of source data cell range
'And obtain address of source data cell range
With SourceSheet.Cells
    LastRow = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    LastCol = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    mySourceData = .Range(.Cells(FirstRow, FirstCol), .Cells(LastRow, LastCol)).Address(ReferenceStyle:=xlR1C1)
End With
'Insert Pivot Table ("Audit_Plan!R6C1:R1048576C93")
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        SourceSheet.Name & "!" & mySourceData, Version:=6).CreatePivotTable tabledestination _
        :=DestSheet.Cells(3, 43), TableName:="PivotTable4", DefaultVersion:=6
    
'Insert Pivot Data
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("QTR")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("Audit_Number"), "Count of Audit_Number", xlCount
    ActiveWindow.SmallScroll ToRight:=6
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Audit_Status")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Control_Rating")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("L1_Area")
        .Orientation = xlRowField
        .Position = 4
    End With
    Range("AQ3").Select
    ActiveSheet.PivotTables("PivotTable4").RowAxisLayout xlTabularRow
    'With ActiveSheet.PivotTables("PivotTable4").PivotFields("QTR")
    '    .PivotItems("(blank)").Visible = False
    'End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Control_Rating")
        .PivotItems("Not Rated").Visible = False
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("L1_Area")
        .PivotItems("Business").Visible = False
    End With
    Range("AU3").Select
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Count of Audit_Number"). _
        Caption = "Count"
    Columns("AQ:AU").EntireColumn.AutoFit
    Range("AJ6").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q1 (Jan - Mar)"",""Audit_Status"",""Completed"")"
    Range("AJ7").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q1 (Jan - Mar)"",""Audit_Status"",""In Progress"")"
    Range("AJ8").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q1 (Jan - Mar)"",""Audit_Status"",""Not Started"")"
    Range("AK6").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q2 (Apr - Jun)"",""Audit_Status"",""Completed"")"
    Range("AK7").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q2 (Apr - Jun)"",""Audit_Status"",""In Progress"")"
    Range("AK8").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q2 (Apr - Jun)"",""Audit_Status"",""Not Started"")"
    Range("AL7").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q3 (Jul - Sep)"",""Audit_Status"",""In Progress"")"
    Range("AL8").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q3 (Jul - Sep)"",""Audit_Status"",""Not Started"")"
    Range("AL6").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q3 (Jul - Sep)"",""Audit_Status"",""Completed"")"
    Range("AM7").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q4 (Oct - Dec)"",""Audit_Status"",""In Progress"")"
    Range("AM8").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q4 (Oct - Dec)"",""Audit_Status"",""Not Started"")"
    Range("AM6").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q4 (Oct - Dec)"",""Audit_Status"",""Completed"")"
    ActiveWindow.SmallScroll Down:=-3
    ActiveWindow.SmallScroll ToRight:=-2
End Sub


Thank you
 
Upvote 0
Which section of the code do you want to condense?
 
Upvote 0
The Pivot Data section at the end of the code?

Code: 
'Insert Pivot Data
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("QTR")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("Audit_Number"), "Count of Audit_Number", xlCount
    ActiveWindow.SmallScroll ToRight:=6
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Audit_Status")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Control_Rating")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("L1_Area")
        .Orientation = xlRowField
        .Position = 4
    End With
    Range("AQ3").Select
    ActiveSheet.PivotTables("PivotTable4").RowAxisLayout xlTabularRow
    'With ActiveSheet.PivotTables("PivotTable4").PivotFields("QTR")
    '    .PivotItems("(blank)").Visible = False
    'End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Control_Rating")
        .PivotItems("Not Rated").Visible = False
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("L1_Area")
        .PivotItems("Business").Visible = False
    End With
    Range("AU3").Select
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Count of Audit_Number"). _
        Caption = "Count"
    Columns("AQ:AU").EntireColumn.AutoFit
    Range("AJ6").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q1 (Jan - Mar)"",""Audit_Status"",""Completed"")"
    Range("AJ7").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q1 (Jan - Mar)"",""Audit_Status"",""In Progress"")"
    Range("AJ8").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q1 (Jan - Mar)"",""Audit_Status"",""Not Started"")"
    Range("AK6").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q2 (Apr - Jun)"",""Audit_Status"",""Completed"")"
    Range("AK7").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q2 (Apr - Jun)"",""Audit_Status"",""In Progress"")"
    Range("AK8").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q2 (Apr - Jun)"",""Audit_Status"",""Not Started"")"
    Range("AL7").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q3 (Jul - Sep)"",""Audit_Status"",""In Progress"")"
    Range("AL8").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q3 (Jul - Sep)"",""Audit_Status"",""Not Started"")"
    Range("AL6").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q3 (Jul - Sep)"",""Audit_Status"",""Completed"")"
    Range("AM7").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q4 (Oct - Dec)"",""Audit_Status"",""In Progress"")"
    Range("AM8").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q4 (Oct - Dec)"",""Audit_Status"",""Not Started"")"
    Range("AM6").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Audit_Number"",R3C43,""QTR"",""Q4 (Oct - Dec)"",""Audit_Status"",""Completed"")"
    ActiveWindow.SmallScroll Down:=-3
    ActiveWindow.SmallScroll ToRight:=-2
    
'Copy Table Data and Paste Special Value
    Range("AJ6:AM8").Copy
    Range("AJ6:AM8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
'Replace [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=REF]#REF[/URL] ! to blanks
    Range("AJ6:AM8").Replace What:="[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=REF]#REF[/URL] !", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("AJ11").Select
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
VBA Calculated Field - Subtract Value in Data Field from Value in Row Field
Replies
0
Views
207
th259
T
S
  • Solved
VBA to create pivot tables
2
Replies
12
Views
1K
Domenic
D
J
  • Question
VBA macro to create pivot table on sheet2 with data source on sheet1
Replies
9
Views
967
SpillerBD
S
S
  • Question
Keep getting an "Error 1004: item could not be found in the OLAP cube" when applying the filter to pivot table
Replies
5
Views
891
RoryA
RoryA
B
  • Solved
Recording macro pivot table error
Replies
2
Views
968
biglb79
B

Forum statistics

Threads
1,214,515
Messages
6,119,973
Members
448,933
Latest member
Bluedbw

Share this page

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
Back
Top