Trying to make a loop to shorten my code

[Loranga]

Aloha, (sorry for my bad english)

I have a sheet where I can controll a pivot table with two buttons on the sheet. I can choose wich week I want to show in the table with a spinbutton. I also have two option buttons that let me choose if I want to see a singel week or accumulate a number of weeks in the table.

I need some help making some form of loop or something that will shorten the code below. (since I would like to have 52 weeks the code will be huge if I do it like below).

This is my code so far, I have only 6 weeks.

Sub Makro1() 
 
Application.ScreenUpdating = False 
    Dim Singleweek_or_Accumulated As Integer 
    Dim Week_number As Integer 
 
    Singleweek_or_Accumulated = Range("J27") 
'   if J27 is = 1 the I will show singel week in the table 
'   if J27 is = 2 the I will show accumulated weeks in the table 
 
    Week_number = Range("J29") 
'   J29 is the cell where i select the week number 
 
    If Singleweek_or_Accumulated = 1 Then 
    ' singel week scenario 
     With ActiveSheet.PivotTables("Pivottabell5").PivotFields("Week") 
     .PivotItems("10").Visible = True 
        .PivotItems("11").Visible = True 
        .PivotItems("12").Visible = True 
        .PivotItems("13").Visible = True 
        .PivotItems("14").Visible = True 
        .PivotItems("15").Visible = True 
    End With 
 
      With ActiveSheet.PivotTables("Pivottabell5").PivotFields("Week") 
    Select Case Week_number 
 
         Case Is = 10 
        .PivotItems("10").Visible = True 
        .PivotItems("11").Visible = False 
        .PivotItems("12").Visible = False 
        .PivotItems("13").Visible = False 
        .PivotItems("14").Visible = False 
        .PivotItems("15").Visible = False 
 
       Case Is = 11 
        .PivotItems("10").Visible = False 
        .PivotItems("11").Visible = True 
        .PivotItems("12").Visible = False 
        .PivotItems("13").Visible = False 
        .PivotItems("14").Visible = False 
        .PivotItems("15").Visible = False 
 
         Case Is = 12 
        .PivotItems("10").Visible = False 
        .PivotItems("11").Visible = False 
        .PivotItems("12").Visible = True 
        .PivotItems("13").Visible = False 
        .PivotItems("14").Visible = False 
        .PivotItems("15").Visible = False 
 
         Case Is = 13 
        .PivotItems("10").Visible = False 
        .PivotItems("11").Visible = False 
        .PivotItems("12").Visible = False 
        .PivotItems("13").Visible = True 
        .PivotItems("14").Visible = False 
        .PivotItems("15").Visible = False 
 
        Case Is = 14 
        .PivotItems("10").Visible = False 
        .PivotItems("11").Visible = False 
        .PivotItems("12").Visible = False 
        .PivotItems("13").Visible = False 
        .PivotItems("14").Visible = True 
        .PivotItems("15").Visible = False 
 
         Case Is = 15 
        .PivotItems("10").Visible = False 
        .PivotItems("11").Visible = False 
        .PivotItems("12").Visible = False 
        .PivotItems("13").Visible = False 
        .PivotItems("14").Visible = False 
        .PivotItems("15").Visible = True 
 
        End Select 
 
   End With 
 
   End If 
 
   If Singleweek_or_Accumulated = 2 Then 
   ' accumulated weeks scenario 
 
    With ActiveSheet.PivotTables("Pivottabell5").PivotFields("Week") 
     .PivotItems("10").Visible = True 
        .PivotItems("11").Visible = True 
        .PivotItems("12").Visible = True 
        .PivotItems("13").Visible = True 
        .PivotItems("14").Visible = True 
        .PivotItems("15").Visible = True 
    End With 
 
      With ActiveSheet.PivotTables("Pivottabell5").PivotFields("Week") 
    Select Case Week_number 
 
         Case Is = 10 
        .PivotItems("10").Visible = True 
        .PivotItems("11").Visible = False 
        .PivotItems("12").Visible = False 
        .PivotItems("13").Visible = False 
        .PivotItems("14").Visible = False 
        .PivotItems("15").Visible = False 
 
       Case Is = 11 
        .PivotItems("10").Visible = True 
        .PivotItems("11").Visible = True 
        .PivotItems("12").Visible = False 
        .PivotItems("13").Visible = False 
        .PivotItems("14").Visible = False 
        .PivotItems("15").Visible = False 
 
         Case Is = 12 
        .PivotItems("10").Visible = True 
        .PivotItems("11").Visible = True 
        .PivotItems("12").Visible = True 
        .PivotItems("13").Visible = False 
        .PivotItems("14").Visible = False 
        .PivotItems("15").Visible = False 
 
         Case Is = 13 
        .PivotItems("10").Visible = True 
        .PivotItems("11").Visible = True 
        .PivotItems("12").Visible = True 
        .PivotItems("13").Visible = True 
        .PivotItems("14").Visible = False 
        .PivotItems("15").Visible = False 
 
        Case Is = 14 
        .PivotItems("10").Visible = True 
        .PivotItems("11").Visible = True 
        .PivotItems("12").Visible = True 
        .PivotItems("13").Visible = True 
        .PivotItems("14").Visible = True 
        .PivotItems("15").Visible = False 
 
         Case Is = 15 
        .PivotItems("10").Visible = True 
        .PivotItems("11").Visible = True 
        .PivotItems("12").Visible = True 
        .PivotItems("13").Visible = True 
        .PivotItems("14").Visible = True 
        .PivotItems("15").Visible = True 
 
        End Select 
 
   End With 
   End If 
 
 
End Sub

[Bob Phillips]

[vba]

Sub Makro1()
Dim Singleweek_or_Accumulated As Integer
Dim Week_number As Integer
Dim PT

Application.ScreenUpdating = False

Singleweek_or_Accumulated = Range("J27")
' if J27 is = 1 the I will show singel week in the table
' if J27 is = 2 the I will show accumulated weeks in the table

Week_number = Range("J29")
' J29 is the cell where i select the week number

Set PT = ActiveSheet.PivotTables("Pivottabell5").PivotFields("Week")
If Singleweek_or_Accumulated = 1 Then
' singel week scenario
Call SetupPivot(PT, True, True, True, True, True, True)


Select Case Week_number

Case Is = 10
Call SetupPivot(PT, True, False, False, False, False, False)

Case Is = 11
Call SetupPivot(PT, False, True, False, False, False, False)

Case Is = 12
Call SetupPivot(PT, False, False, True, False, False, False)

Case Is = 13
Call SetupPivot(PT, False, False, False, True, False, False)

Case Is = 14
Call SetupPivot(PT, False, False, False, False, True, False)

Case Is = 15
Call SetupPivot(PT, False, False, False, False, False, True)

End Select

End If

If Singleweek_or_Accumulated = 2 Then
' accumulated weeks scenario

Call SetupPivot(PT, True, True, True, True, True, True)

Select Case Week_number

Case Is = 10
Call SetupPivot(PT, True, False, False, False, False, False)

Case Is = 11
Call SetupPivot(PT, True, True, False, False, False, False)

Case Is = 12
Call SetupPivot(PT, True, True, True, False, False, False)

Case Is = 13
Call SetupPivot(PT, True, True, True, True, False, False)

Case Is = 14
Call SetupPivot(PT, True, True, True, True, True, False)

Case Is = 15
Call SetupPivot(PT, True, True, True, True, True, True)

End Select

End If

End Sub

Private Sub SetupPivot(ByRef PT, ParamArray VisibleFlags())
Dim i As Long
With PT
For i = 10 To 15
.PivotItems(CStr(i)).Visible = VisibleFlags(i - 10)
Next i
End With
End Sub
[/vba]

[Norie]

Cross post.