Poundland
08-17-2010, 02:32 AM
Guys,
I have written some code, an extract below, whereby I need to Pivot data in a Pivot Table to only show certian date ranges, my problem is that where there are more dates than are defined in my Code it does not filter, in other words it will show all available dates and not just filter and show the dates I have selected.
How can I get around this?
Dim today(1 To 8) As String
today1 = Format(Now - 1, "dd/mm/yyyy")
today2 = Format(Now - 2, "dd/mm/yyyy")
today3 = Format(Now - 3, "dd/mm/yyyy")
today4 = Format(Now - 4, "dd/mm/yyyy")
today5 = Format(Now - 5, "dd/mm/yyyy")
today6 = Format(Now - 6, "dd/mm/yyyy")
today7 = Format(Now - 7, "dd/mm/yyyy")
today8 = Format(Now - 8, "dd/mm/yyyy")
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Delivery Date")
On Error Resume Next
.PivotItems(today1).Visible = True
.PivotItems(today2).Visible = True
.PivotItems(today3).Visible = True
.PivotItems(today4).Visible = True
.PivotItems(today5).Visible = True
.PivotItems(today6).Visible = True
.PivotItems(today7).Visible = True
.PivotItems(today8).Visible = True
On Error GoTo 0
End With
I have written some code, an extract below, whereby I need to Pivot data in a Pivot Table to only show certian date ranges, my problem is that where there are more dates than are defined in my Code it does not filter, in other words it will show all available dates and not just filter and show the dates I have selected.
How can I get around this?
Dim today(1 To 8) As String
today1 = Format(Now - 1, "dd/mm/yyyy")
today2 = Format(Now - 2, "dd/mm/yyyy")
today3 = Format(Now - 3, "dd/mm/yyyy")
today4 = Format(Now - 4, "dd/mm/yyyy")
today5 = Format(Now - 5, "dd/mm/yyyy")
today6 = Format(Now - 6, "dd/mm/yyyy")
today7 = Format(Now - 7, "dd/mm/yyyy")
today8 = Format(Now - 8, "dd/mm/yyyy")
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Delivery Date")
On Error Resume Next
.PivotItems(today1).Visible = True
.PivotItems(today2).Visible = True
.PivotItems(today3).Visible = True
.PivotItems(today4).Visible = True
.PivotItems(today5).Visible = True
.PivotItems(today6).Visible = True
.PivotItems(today7).Visible = True
.PivotItems(today8).Visible = True
On Error GoTo 0
End With