PDA

View Full Version : Solved: auto filter pivot table



CCkfm2000
10-28-2005, 02:29 AM
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.

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



Any help will be much appreciated. :p

CCkfm2000
10-28-2005, 04:57 AM
solved...
thanks to all.
:thumb

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