Hello,
I have a workbook with a tab (Individual) containing a cell (PT_Individual_ID), that is a specific customer ID, and 2 pivot tables which read a database contained in the second tab of the workbook. I want to be able to enter the customer ID and have the filters on the pivot tables update to filter based on the customer ID entered. Here's what I have so far - I get an error when it reaches the final step - any suggestions?
Sub auto_open()
ThisWorkbook.Worksheets("Individual").OnEntry = "INDV_ID"
End Sub
Sub INDV_ID()
Dim KeyCells As String
'Define which cells should trigger the KeyCellsChanged macro
KeyCells = "PT_Individual_ID"
'If the Activecell is one of the key cells, call the KeyCellsChanged macro
If Not Application.Intersect(ActiveCell, Range(KeyCells)) _
Is Nothing Then KeyCellsChanged
End Sub
Sub KeyCellsChanged()
Sheets("Individual").PivotTables("PivotTable1").PivotFields("CUST_ID_NUM").ClearAllFilters
Sheets("Individual").PivotTables("PivotTable1").PivotFields("CUST_ID_NUM").CurrentPage _
= Worksheets("Individual").Range("PT_Individual_ID").Value
Sheets("Individual").PivotTables("PivotTable2").PivotFields("CUST_ID_NUM").ClearAllFilters
Sheets("Individual").PivotTables("PivotTable2").PivotFields("CUST_ID_NUM").CurrentPage _
= Range("PT_Individual_ID").Value
End Sub
I have a workbook with a tab (Individual) containing a cell (PT_Individual_ID), that is a specific customer ID, and 2 pivot tables which read a database contained in the second tab of the workbook. I want to be able to enter the customer ID and have the filters on the pivot tables update to filter based on the customer ID entered. Here's what I have so far - I get an error when it reaches the final step - any suggestions?
Sub auto_open()
ThisWorkbook.Worksheets("Individual").OnEntry = "INDV_ID"
End Sub
Sub INDV_ID()
Dim KeyCells As String
'Define which cells should trigger the KeyCellsChanged macro
KeyCells = "PT_Individual_ID"
'If the Activecell is one of the key cells, call the KeyCellsChanged macro
If Not Application.Intersect(ActiveCell, Range(KeyCells)) _
Is Nothing Then KeyCellsChanged
End Sub
Sub KeyCellsChanged()
Sheets("Individual").PivotTables("PivotTable1").PivotFields("CUST_ID_NUM").ClearAllFilters
Sheets("Individual").PivotTables("PivotTable1").PivotFields("CUST_ID_NUM").CurrentPage _
= Worksheets("Individual").Range("PT_Individual_ID").Value
Sheets("Individual").PivotTables("PivotTable2").PivotFields("CUST_ID_NUM").ClearAllFilters
Sheets("Individual").PivotTables("PivotTable2").PivotFields("CUST_ID_NUM").CurrentPage _
= Range("PT_Individual_ID").Value
End Sub