CODEWITHSUNDEEP
vbaexcel
MCP_infiltrator
MCP_infiltrator

Reputation: 4179

VBA Loop Through Pivot Table Excel 2007

I have code that loops through a list on one worksheet and then is to pick an item from a corresponding pivot table, which does not work. The pivot tables are created by a macro that I made, the pivot tables are produced just fine. For the life of me though I cannot figure out why when I set the CurrentPage portion of the pivot table equal to my variable it does not set it. Here is the code that I am using to loop:

Sub m4_HCAHPS_Macro()

Dim vPhys2 As String
Dim vrow2 As Long
Dim vlastphys2 As String

vrow2 = 1

nextRow2:

Sheets("hcahps doctors").Activate
Range("A" & CStr(vrow2)).Select

vPhys2 = ActiveCell.Value

If Len(vPhys2) < 1 Then
    MsgBox "All Done Here"
    GoTo subcomplete
End If

Sheets("hcahps").Activate
With ActiveSheet.PivotTables("HcahpsPivotcurrentTable").PivotFields("Doctor").CurrentPage = vPhys2
End With

With ActiveSheet.PivotTables("HcahpsPivotTrendTable").PivotFields("Doctor").CurrentPage = vPhys2
End With

Sheets("hcahps report").Activate

vrow2 = vrow2 + 1
vlastphys2 = vPhys2

GoTo nextRow2

subcomplete:

Exit Sub

End Sub

Any suggestions would be greatly appreciated

Upvotes: 1

Views: 1871

Answers (1)

Automate This
Automate This

Reputation: 31364

Here are some tips for rewriting this code to be a bit easier to follow and doesn't use common pitfalls of Active and goto statements.

Sub m4_HCAHPS_Macro()

    Dim vPhys2 As String
    Dim vrow2 As Long: vrow2 = 1
    Dim vlastphys2 As String

    Dim wksDoctors As Worksheet
    Dim wkshcahps As Worksheet

    Set wkshcahps = Sheets("hcahps")
    Set wksDoctors = Sheets("hcahps doctors")

    vPhys2 = wksDoctors.Range("A" & CStr(vrow2)).Value

    Do While (Len(vPhys2) < 1)
        wkshcahps.PivotTables("HcahpsPivotcurrentTable").PivotFields("Doctor").CurrentPage = vPhys2
        wkshcahps.PivotTables("HcahpsPivotTrendTable").PivotFields("Doctor").CurrentPage = vPhys2

        vrow2 = vrow2 + 1
        vlastphys2 = vPhys2

        vPhys2 = wksDoctors.Range("A" & CStr(vrow2)).Value
    Loop

    MsgBox "All Done Here"
End Sub

As @simoco said, your with statment wasn't set properly. You can't set a value in the with statement it self. You can set it within the with block like this if you wanted to:

With ActiveSheet.PivotTables("HcahpsPivotTrendTable").PivotFields("Doctor")
    .CurrentPage = vPhys2
End With

Upvotes: 2

Related Questions