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:
This is the section of the code where the error message appears:
Thank you
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