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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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

J
  • Solved
VBA/Macro: Adding Pivot table with connection string
Replies
1
Views
155
Jette3173
J
S
  • Question Question
Macro for
Replies
2
Views
96
Sanderb
S
D
  • Question Question
VBA Coding for adding Pivot Table with changing data table
Replies
6
Views
192
darmst24
D
C
  • Solved
VBA Add Pivot Table Sheet Name
Replies
1
Views
175
Anthony47
A
C
  • Question Question
Pivot Table VBA
Replies
0
Views
230
chaddres
C

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,619
Latest member
Shiv1198

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