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
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