Solved: auto filter pivot table

[CCkfm2000]

Hello,

I have 2 identical pivot tables in a sheet.
I need the user to enter 3 dates

E.g.

Current week = 05/43
Last week = 05/42
Fortnight = 05/41

Once entered I need the left pivot table filter out the 3 dates out.
On the right I need to filter out all other dates plus the current week.

I've managed to get this far with the code.

[VBA]Dim curweek As String
Dim firstweek As String
Dim secondweek As String

Sub filtertables()

firstweek = InputBox("Enter 1 of the last two weeks e.g 05/41")
secondweek = InputBox("Enter 2 of the last two weeks e.g 05/42")
curweek = InputBox("Enter current week e.g 05/43")

'left table
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Week")
.PivotItems(curweek).Visible = False
.PivotItems(firstweek).Visible = False
.PivotItems(secondweek).Visible = False
End With

'right table

With ActiveSheet.PivotTables("PivotTable2").PivotFields("Week")
'.PivotItems(curweek).Visible =
.PivotItems(firstweek).Visible = True
.PivotItems(secondweek).Visible = True
End With

End Sub
[/VBA]


Any help will be much appreciated.

[CCkfm2000]

solved...
thanks to all.


[VBA]Dim curweek As String
Dim firstweek As String
Dim secondweek As String
Dim i As Integer
Sub filtertables()
firstweek = InputBox("Enter 1 of the last two weeks e.g 05/41")
secondweek = InputBox("Enter 2 of the last two weeks e.g 05/42")
curweek = InputBox("Enter current week e.g 05/43")

'left table
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Week")
.PivotItems(curweek).Visible = False
.PivotItems(firstweek).Visible = False
.PivotItems(secondweek).Visible = False
End With
'right table
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Week")
For i = 1 To .PivotItems.Count - 4
.PivotItems(i).Visible = False
Next
.PivotItems(curweek).Visible = False
End With
End Sub[/VBA]