I have two PivotFields. One will be a PageField while the other is a RowField. My macro will switch them, while also grabbing values from the top of the page and using these values as the CurrentPage for each Field. I get an error when running the macro that says Error 1004: Unable to Get the CurrentPage Property of the PivotField Class. It can set the PageField Class CurrentPage, but not the RowField. Here is my code (look for my comments)...
Thanks if you can help me out! I can't continue my project until I figure this out.
Code:
Sub EWOCountPillar_RC()
Dim sh As Worksheet
Dim pt As PivotTable
Set sh = Sheets("EWO Count Table")
Set pt = sh.PivotTables("pt3")
If Application.ScreenUpdating = True Then Application.ScreenUpdating = False
If pt.ManualUpdate = False Then pt.ManualUpdate = True
Dim pf1 As PivotField, pf2 As PivotField
Set pf1 = pt.PivotFields("WCM_Pillar")
Set pf2 = pt.PivotFields("Root_Cause")
If pf1.Orientation = xlColumnField Then
pf1.Orientation = xlHidden
pf2.Orientation = xlColumnField
Else
pf2.Orientation = xlHidden
pf1.Orientation = xlColumnField
End If
If Application.ScreenUpdating = False Then Application.ScreenUpdating = True
If pt.ManualUpdate = True Then pt.ManualUpdate = False
End Sub
Sub EWOCountPA_Mach()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim pt As PivotTable
Set sh1 = Sheets("EWO Count Table")
Set pt = sh1.PivotTables("pt3")
If Application.ScreenUpdating = True Then Application.ScreenUpdating = False
If pt.ManualUpdate = False Then pt.ManualUpdate = True
Dim r1 As Range, r2 As Range
Dim str1 As String, str2 As String
Dim pf1 As PivotField, pf2 As PivotField
Set sh2 = Sheets("EWO Count")
Set r1 = sh2.Range("F1")
Set r2 = sh2.Range("H1")
str1 = r1.Value
str2 = r2.Value
Set pf1 = pt.PivotFields("Process_Area")
Set pf2 = pt.PivotFields("Machine")
If pf1.Orientation = xlRowField Then
pf1.ClearAllFilters
pf1.Orientation = xlPageField
pf2.Orientation = xlRowField
pf2.AutoSort xlDescending, "Count of EWO"
str1 = r1.Value
If r2.Value = "(All)" Then
str2 = "(Select All)"
Else
str2 = r2.Value
End If
pf1.CurrentPage = str1
pf2.ClearAllFilters
pf2.CurrentPage = str2 'Here is the Error
Else
pf2.ClearAllFilters
pf1.Orientation = xlRowField
pf2.Orientation = xlPageField
pf1.AutoSort xlDescending, "Count of EWO"
str2 = r2.Value
If r1.Value = "(All)" Then
str1 = "(Select All)"
Else
str1 = r1.Value
End If
pf2.CurrentPage = str2
pf1.ClearAllFilters
pf1.CurrentPage = str1 'Here is the Error
End If
If Application.ScreenUpdating = False Then Application.ScreenUpdating = True
If pt.ManualUpdate = True Then pt.ManualUpdate = False
End Sub