Snasui Excel Forum

Code: Select all

Sub akemth()
Dim a, aa As Integer
Dim b As String
Dim i As Integer
Dim ii As Integer

Application.ScreenUpdating = False
    ActiveWorkbook.RefreshAll
    
aa = Month(Date)
a = aa
b = Format(a, "00")
For ii = 1 To 4
    Sheets(ii).Select
        'Sheet1.Select
    If ii = 1 Then
             With ActiveSheet.PivotTables("PivotTable1").PivotFields("Jusify_Month ")
                  .PivotItems(b).Visible = True
                  For i = a To 12
                                     a = a - 1
                                     If a >= 1 And a <= 12 Then
                                             b = Format(a, "00")
                                            .PivotItems(b).Visible = False
                                    End If
                  Next i
                  a = aa
              End With
        ElseIf ii = 2 Then
            With ActiveSheet.PivotTables("PivotTable2").PivotFields("OnScrMonth2")
                 .PivotItems(b).Visible = True
                  For i = a To 12
                                     a = a - 1
                                     If a >= 1 And a <= 12 Then
                                             b = Format(a, "00")
                                            .PivotItems(b).Visible = False
                                    End If
                  Next i
                  a = aa
             End With
        ElseIf ii = 3 Then
             With ActiveSheet.PivotTables("PivotTable3").PivotFields("Jusify_Month2")
             .PivotItems(b).Visible = True
                  For i = a To 12
                                     a = a - 1
                                     If a >= 1 And a <= 12 Then
                                             b = Format(a, "00")
                                            .PivotItems(b).Visible = False
                                    End If
                  Next i
                  a = aa
            End With
        ElseIf ii = 4 Then
             With ActiveSheet.PivotTables("PivotTable4").PivotFields("OnScrMonth2")
             .PivotItems(b).Visible = True
                  For i = a To 12
                                     a = a - 1
                                     If a >= 1 And a <= 12 Then
                                             b = Format(a, "00")
                                            .PivotItems(b).Visible = False
                                    End If
                  Next i
                  a = aa
            End With
        End If
    Next ii
     
ActiveWorkbook.Save
End Sub

Code: Select all

Sub test()
Dim a, aa As Integer
Dim b As String
Dim i As Integer
Dim ii As Integer

Application.ScreenUpdating = False
    ActiveWorkbook.RefreshAll
    
aa = Month(Date)
a = aa
b = Format(a, "00")
For ii = 1 To 4
    Sheets(ii).Select
        
    If ii = 1 Then
             With ActiveSheet.PivotTables("PivotTable"&ii).PivotFields(i)
                  .PivotItems(b).Visible = True
                  For i = a To 12
                                     a = a - 1
                                     If a >= 1 And a <= 12 Then
                                             b = Format(a, "00")
                                            .PivotItems(b).Visible = False
                                    End If
                  Next i
                  a = aa
              End With
End Sub