0

I have master sheet in which data from various "options" sheets pulled now I want to refresh all "options" sheets to get refresh on specific interval for that I have following vba code in master sheet

Public interval As Double
Sub Dosomething()
Dim xSh As Worksheet
Application.ScreenUpdating = False
For Each xSh In Worksheets
    xSh.Select
    Call macro_timer
Next
Application.ScreenUpdating = True
End Sub

Sub macro_timer()
 interval = Now + TimeValue("00:03:00")
 Application.OnTime interval, "my_macro"
 End Sub


Sub my_macro()
 For Each wb In Application.Workbooks: wb.RefreshAll: Next wb
 Call macro_timer
 End Sub

However code is not working as I wanted somewhere I am lacking, don't know where. pls help.thx

braX
  • 11,506
  • 5
  • 20
  • 33
Arshit patel
  • 133
  • 1
  • 10
  • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Nov 13 '19 at 09:24

1 Answers1

1

You could try:

  1. Loop all open workbooks
  2. Refresh

Code:

Option Explicit

Sub LoopOpenWorkbook()

    Dim wb As Workbook

    For Each wb In Application.Workbooks

        wb.RefreshAll

    Next wb

End Sub
Error 1004
  • 7,877
  • 3
  • 23
  • 46