Forum Discussion

Con13201328

Copper Contributor

Jun 23, 2022

VBA for doing same action in a workbook

Hi all, i am not familiar with VBA but i would like to refresh for every tab in a single workbook. My workbook have difference numbers of sheet from time to time, so i can't use the record functi...

excel

Macros and VBA

NikolinoDE

Platinum Contributor

Jun 26, 2022

Con13201328

I cannot tell from the message whether it is about updating external data and connections or whether it is about worksheet formulas. Anyway, here is a solution approach.

Sub Query_aktual ()
ActiveWorkbook.RefreshAll
For Each qt In ActiveSheet.QueryTables
qt.Refresh (BackgroundQuery)
Next
End Sub

If it is not what you had in mind, I ask you for more information. Information such as Excel version, operating system, storage medium. preferably with a file (without sensitive data) or photos.

Hope I was able to help you :).

NikolinoDE

I know I don't know anything (Socrates)

mtarler

Silver Contributor

Jun 26, 2022

NikolinoDE , hi Niko, I also not sure what they need but was wondering if your macro should also span all worksheets. something like this?

Sub Query_aktual ()
Dim sh as Worksheets
ActiveWorkbook.RefreshAll
For s = 1 to ActiveWorkbook.sheets.count
  For Each qt In ActiveWorkbook.sheets(s).QueryTables
    qt.Refresh (BackgroundQuery)
  Next
Next
End Sub

Con13201328

Copper Contributor

Jun 27, 2022

Hi! mtarler

I have googled something similar but it is not running 😞

Dim i As Integer
Dim j As Integer
For i = 1 To Workbooks.Count
For j = 1 To Workbooks(i).Worksheets.Count
Workbooks(i).Worksheets(j).Application.Run"ImportWorksheet"
Next j
Next i
End Sub