Loop broken

[jack3d]

I have written a program to take data from a pivot table and put it into another spreadsheet to be used for static graphs. The goal is for it to loop by month, copy the data for each month, and then move on to the next one. However, the loop only works for Month 1. When it gets to months 2-8 the Selection.Copy function seems to stop working, even though the rest of the code runs.

Help please.

For w = 1 To Month(Date)
    
    For pl = 1 To Month(Date)
    Sheets("PivotTable").PivotTables("PivotData").PivotFields("Month").PivotItems(pl).Visible = False
    Next pl
    
    For i = 1 To Month(Date)
    If i = w Then
    Sheets("PivotTable").PivotTables("PivotData").PivotFields("Month").PivotItems(i).Visible = True
    End If
    
    Sheets("PivotTable").PivotTables("PivotData").PivotFields("Month").PivotItems("").Visible = False
    Sheets("PivotTable").PivotTables("PivotData").PivotFields("Month").PivotItems("(blank)").Visible = False
        

    With Sheets("PivotTable").PivotTables("PivotData").PivotFields("Month")
        .Orientation = xlPageField
        .Position = 2
    End With

    Sheets("PivotTable").PivotTables("PivotData").PivotSelect "", xlDataAndLabels, True
    Selection.Copy
    Sheets("PivotData").Select
    Range("A1").Select
    ActiveCell.Offset(0, 6 * (w - 1)).Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    

    With Sheets("PivotTable").PivotTables("PivotData").PivotFields("Month")
        .Orientation = xlRowField
        .Position = 1
    End With

Next i
 Next w

 

[Jerry Sullivan]

Hi,

Try the code below. It sets .EnableMultiplePageItems = False which allows you to show just one PageField at a time without having to hide all the others.

This might not do exactly what you were trying to do, but it might point you in the direction of a simpler approach.

Sub LoopThroughPTMonths()
    Dim w As Long
    Application.ScreenUpdating = False
    With Sheets("PivotTable").PivotTables("PivotData").PivotFields("Month")
        .Orientation = xlPageField
        .EnableMultiplePageItems = False
        .Position = 2
        For w = 1 To Month(Date)
            .CurrentPage = w
            Sheets("PivotTable").PivotTables("PivotData").TableRange2.Copy
            Sheets("PivotData").Range("A1").Offset(0, 6 * (w - 1)) _
                .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=False
        Next w
        .Orientation = xlRowField
        .Position = 1
    End With
End Sub

 

[jack3d]

Hi,

Try the code below. It sets .EnableMultiplePageItems = False which allows you to show just one PageField at a time without having to hide all the others.

This might not do exactly what you were trying to do, but it might point you in the direction of a simpler approach.

Sub LoopThroughPTMonths()
    Dim w As Long
    Application.ScreenUpdating = False
    With Sheets("PivotTable").PivotTables("PivotData").PivotFields("Month")
        .Orientation = xlPageField
        .EnableMultiplePageItems = False
        .Position = 2
        For w = 1 To Month(Date)
            .CurrentPage = w
            Sheets("PivotTable").PivotTables("PivotData").TableRange2.Copy
            Sheets("PivotData").Range("A1").Offset(0, 6 * (w - 1)) _
                .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=False
        Next w
        .Orientation = xlRowField
        .Position = 1
    End With
End Sub

worked beautifully, thank you