[RESOLVED] VB Code Confuses Excel When Multiple Dashboards Are Open

[Karlin]

I am very new to VB code (1 week of just looking at it, and modified one line of code below). I am using Excel 2010, and VBA 7.0. I have created 2 very similar dashboards where only the data is different, otherwise, the dashboards look exactly the same (the Pivot Table names are named differently though). I used the code below in one dashboard so pivot tables would automatically update when the data in a table is automatically refreshed, and it worked great. However, when I opened the second dashboard without any VBA code while the first one was still opened, I got the following error:

Run-time error '1004'
Unable to get the PivotTables property of the Worksheet class

When I click the Debug button, I see the entire third line highlighted in yellow:

******************************************
Private Sub Worksheet_Change(ByVal Target As Range)

Worksheets("Sheet 1").PivotTables("PivotTable5").PivotCache.Refresh
Worksheets("Sheet 2").PivotTables("PivotTable6").PivotCache.Refresh
Worksheets("Sheet 3").PivotTables("PivotTable7").PivotCache.Refresh

End Sub
*****************************************

Is there some way to associate the above code to a particular dashboard so I can have multiple dashboards open without any problems?

[westconn1]

are the dashboards in separate workbooks?

[Karlin]

Yes, they are in separate workbooks.

[westconn1]

try prefixing the pivot tables with the workbook object like

workbooks("mybook.xls").Worksheets("Sheet 1").PivotTables("PivotTable5").PivotCache.Refresh

or just use the thisworkbook object

[Karlin]

Pete, I have created a third dashboard, and put your code suggestion in all three dashboards. They are all automatically updating and co-existing happily alongside each other. Thank you for your help.