Good morning,
I have an Excel 2007 workbook containing 2 sheets, Sheet1 contains 2 pivots linked to an Access database, Sheet2 contains references to those 2 pivots to create a Summary.
What I'm looking to do is have a cell on the Summary sheet in which i can type a week ending date, i then want the 2 pivots to be filitered to this date AND the previous week.
For example, i type 09/05/2010 in the cell, both pivots update to show data for WE 09/05/2010 and WE 02/05/2010.
I've found the code below which seems to work but will only update the pivots to the value in the cell, I can't see how to make it select a second value (my VBA skills are very basic).
If anyone can help i'll be your freiend for life!
Current code im using:
Const RegionRangeName As String = "Date3"
Const PivotTableName As String = "PivotTable3"
Const PivotFieldName As String = "WE_Date"
Public Sub UpdatePivotFieldFromRange(RangeName As String, FieldName As String, _
PivotTableName As String)
Dim rng As Range
Set rng = Application.Range("Date3")
Dim pt As PivotTable
Dim Sheet As Worksheet
For Each Sheet In Application.ActiveWorkbook.Worksheets
On Error Resume Next
Set pt = Sheet.PivotTables("PivotTable3")
Next
If pt Is Nothing Then GoTo Ex
On Error GoTo Ex
pt.ManualUpdate = True
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim Field As PivotField
Set Field = pt.PivotFields("WE_Date")
Field.ClearAllFilters
Field.EnableItemSelection = True
SelectPivotItem Field, rng.Text
pt.RefreshTable
Ex:
pt.ManualUpdate = False
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Public Sub SelectPivotItem(Field As PivotField, ItemName As String)
Dim Item As PivotItem
For Each Item In Field.PivotItems
Item.Visible = (Item.Caption = ItemName)
Next
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Target, Application.Range(RegionRangeName)) _
Is Nothing Then
UpdatePivotFieldFromRange _
RegionRangeName, PivotFieldName, PivotTableName
End If
End Sub
I have an Excel 2007 workbook containing 2 sheets, Sheet1 contains 2 pivots linked to an Access database, Sheet2 contains references to those 2 pivots to create a Summary.
What I'm looking to do is have a cell on the Summary sheet in which i can type a week ending date, i then want the 2 pivots to be filitered to this date AND the previous week.
For example, i type 09/05/2010 in the cell, both pivots update to show data for WE 09/05/2010 and WE 02/05/2010.
I've found the code below which seems to work but will only update the pivots to the value in the cell, I can't see how to make it select a second value (my VBA skills are very basic).
If anyone can help i'll be your freiend for life!
Current code im using:
Const RegionRangeName As String = "Date3"
Const PivotTableName As String = "PivotTable3"
Const PivotFieldName As String = "WE_Date"
Public Sub UpdatePivotFieldFromRange(RangeName As String, FieldName As String, _
PivotTableName As String)
Dim rng As Range
Set rng = Application.Range("Date3")
Dim pt As PivotTable
Dim Sheet As Worksheet
For Each Sheet In Application.ActiveWorkbook.Worksheets
On Error Resume Next
Set pt = Sheet.PivotTables("PivotTable3")
Next
If pt Is Nothing Then GoTo Ex
On Error GoTo Ex
pt.ManualUpdate = True
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim Field As PivotField
Set Field = pt.PivotFields("WE_Date")
Field.ClearAllFilters
Field.EnableItemSelection = True
SelectPivotItem Field, rng.Text
pt.RefreshTable
Ex:
pt.ManualUpdate = False
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Public Sub SelectPivotItem(Field As PivotField, ItemName As String)
Dim Item As PivotItem
For Each Item In Field.PivotItems
Item.Visible = (Item.Caption = ItemName)
Next
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Target, Application.Range(RegionRangeName)) _
Is Nothing Then
UpdatePivotFieldFromRange _
RegionRangeName, PivotFieldName, PivotTableName
End If
End Sub