[RESOLVED] VB Code Confuses Excel When Multiple Dashboards Are Open-VBForums
Results 1 to 5 of 5

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

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2017
    Posts
    6

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

    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?

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    22,887

    Re: VB Code Confuses Excel When Multiple Dashboards Are Open

    are the dashboards in separate workbooks?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Jun 2017
    Posts
    6

    Re: VB Code Confuses Excel When Multiple Dashboards Are Open

    Yes, they are in separate workbooks.

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    22,887

    Re: VB Code Confuses Excel When Multiple Dashboards Are Open

    try prefixing the pivot tables with the workbook object like

    Code:
    workbooks("mybook.xls").Worksheets("Sheet 1").PivotTables("PivotTable5").PivotCache.Refresh
    or just use the thisworkbook object
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    New Member
    Join Date
    Jun 2017
    Posts
    6

    Re: VB Code Confuses Excel When Multiple Dashboards Are Open

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.