Problem with VBA button to refresh worksheets

[fuzzyjonclay]

Hi everyone

I've created a button (with a VBA script behind it) to update all worksheets in my spreadsheet.

The problems I'm having are as follows:

1. Even though I'm asking the script to take me back to the "DONUT VIEW" sheet it isn't. It does briefly but then seems to run through the entire script again and lands me on another worksheet within the spreadsheet.
2. The script doesn't work at all if I subsequently hide the worksheets. What would I need to do in order to resolve this?

Thank you for helping with this!

My script is shown below.

Best wishes
Jon

Sub data_update()
'
' data_update Macro
'
'
Sheets("DATA - Unapproved Income").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Sheets("DATA - School1").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets("DATA - School2").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Sheets("DATA - School3").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Sheets("DATA - School4").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets("PIVOTS").Select
Range("A6").Select
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
Range("A25").Select
ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh
Range("D16").Select
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
Range("G2").Select
ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
Range("G10").Select
ActiveSheet.PivotTables("PivotTable7").PivotCache.Refresh
Range("G20").Select
ActiveSheet.PivotTables("PivotTable8").PivotCache.Refresh
Range("G28").Select
ActiveSheet.PivotTables("PivotTable9").PivotCache.Refresh
Range("G36").Select
ActiveSheet.PivotTables("PivotTable10").PivotCache.Refresh
Range("G44").Select
ActiveSheet.PivotTables("PivotTable11").PivotCache.Refresh
Sheets("DONUT VIEW").Select
End Sub

 

[6StringJazzer]

This does not work on hidden sheets because you Select each sheet to work on it, which is not necessary. See revised code below. Your code appears to be right from the macro recorder so suffers from macro recorder bloat.

I don't know why it would run the entire script again. Are you calling this from other code, or invoking it from a button on a sheet? If you have other code, please show it.

Sub data_update()   '
   ' data_update Macro
   '
   
   Sheets("DATA - Unapproved Income").ListObject.QueryTable.Refresh BackgroundQuery:=False
   Sheets("DATA - School1").ListObject.QueryTable.Refresh BackgroundQuery:=False
   Sheets("DATA - School2").ListObject.QueryTable.Refresh BackgroundQuery:=False
   Sheets("DATA - School3").ListObject.QueryTable.Refresh BackgroundQuery:=False
   Sheets("DATA - School4").ListObject.QueryTable.Refresh BackgroundQuery:=False
   
   With Sheets("PIVOTS")
      .PivotTables("PivotTable2").PivotCache.Refresh
      .PivotTables("PivotTable5").PivotCache.Refresh
      .PivotTables("PivotTable4").PivotCache.Refresh
      .PivotTables("PivotTable3").PivotCache.Refresh
      .PivotTables("PivotTable7").PivotCache.Refresh
      .PivotTables("PivotTable8").PivotCache.Refresh
      .PivotTables("PivotTable9").PivotCache.Refresh
      .PivotTables("PivotTable10").PivotCache.Refresh
      .PivotTables("PivotTable11").PivotCache.Refresh
   End With
   
   If Sheets("DONUT VIEW").Visible = xlSheetVisible Then Sheets("DONUT VIEW").Activate
   
End Sub

Note: Please use code tags on code to preserve formatting

 

[fuzzyjonclay]

Many thanks for your reply.

Yes, I'm invoking the script from a button that I've placed on the DONUT VIEW worksheet. Is there a better way to invoke the script?

 

[6StringJazzer]

Yes, I'm invoking the script from a button that I've placed on the DONUT VIEW worksheet. Is there a better way to invoke the script?

No, that's fine. I was just wondering if you were calling it from a Sub that had other code that was causing it to get called more than once. I also wondered if you have a Worksheet_Change Sub in any of those sheets that could also be causing trouble.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...button-to-refresh-worksheets.html#post5010834

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.