Excel 2007 VBA Pivot Tables

[hshure]

Just want to start out by saying that I love this forum. You guys have saved my butt countless times.

I have created a pivot table in Excel 2007 and I am trying to use VBA to allow the end user to change the pivot table based on radio buttons on the sheet. I have utilized code found throughout the internet as I am new to VBA. I am now stuck as I cannot determine how to set mutliple values in the TIER1 page field. Also, I am trying to set the LAST_STEP_COMPLETED page field value to whatever the PROCESS_DT page field value is.

Here is the code that I have for one of the pivot reports. I really appreciate your help.


Sub O69Button_Click()
On Error Resume Next


Dim myPivot As PivotTable
Dim myField As PivotField
Dim myItem As PivotItem
Dim myRange As Range
Set myPivot = ActiveSheet.PivotTables(1)

'Clears all the pivot fields

For Each myField In myPivot.DataFields
myField.Orientation = xlHidden
Next myField
For Each myField In myPivot.RowFields
myField.Orientation = xlHidden
Next myField
For Each myField In myPivot.ColumnFields
myField.Orientation = xlHidden
Next myField
For Each myField In myPivot.PageFields
myField.Orientation = xlHidden
Next myField

Set myField = myPivot.PivotFields("Loan_Number")
myField.Orientation = xlDataField
myField.Function = xlCount

myPivot.AddFields RowFields:=Array("As Of", "LEVEL_10_MGR", "DIRECT_MGR", "UW"), _
ColumnFields:=("O69"), PageFields:=Array("LAST_STEP_COMPLETED", "LAST_STEP_DATE", "TIER1", "MAIN_GROUP", "BUSOWNER3_QUE", "PROCESS_DT")

ActiveSheet.PivotTables("PivotTable1").PivotFields("TIER1"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("TIER1"). _
CurrentPage = "UW REJECT TO RM"
'figure out how to set the TIER1 to 10.1, BAUMOD, MOD DOCS REJECTED

ActiveSheet.PivotTables("PivotTable1").PivotFields("LAST_STEP_COMPLETED"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("LAST_STEP_COMPLETED"). _
CurrentPage = "O69"

Dim strPage As String
strPage = ActiveSheet.Range("B1")
ActiveSheet.PivotTables("PivotTable1").PivotFields("LAST_STEP_DATE"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("LAST_STEP_DATE"). _
CurrentPage = strPage

Columns("A:A").ColumnWidth = 35
Columns("B:B").ColumnWidth = 22
Columns("D:D").ColumnWidth = 22
Columns("E:E").ColumnWidth = 22
Columns("B:Z").HorizontalAlignment = xlCenter
Rows("7:7").WrapText = True

End Sub