Forum Discussion

Con13201328's avatar
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's avatar
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)

Con13201328's avatar
Con13201328
Copper Contributor
Jun 27, 2022

NikolinoDE 

 

Thanks for your reply! 

Below is i figured how to make the application run.

However, i have to write the code and select the tab one by one.

 

Sub Import_alltabs()
'
' Import_alltabs Macro
'

'
    Sheets("IFRS16").Select
    Application.Run "ImportWorksheet"
    
    Sheets("GP").Select
    Application.Run "ImportWorksheet"

    Sheets("Revenue").Select
    Application.Run "ImportWorksheet"
  
    Sheets("Cost").Select
    Application.Run "ImportWorksheet"

   ActiveWorkbook.Save
End Sub

 

 

I have googled this but not 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

 

 

  • mtarler's avatar
    mtarler
    Silver Contributor
    Jun 27, 2022

    Con13201328  you can't just tack "Application.Run" onto the end like that.  you could try:

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

    But it would be BETTER if you modified "ImportWorksheet" macro to include this looping directly or at least include a parameter to send it a reference to the sheet.  Doing this .SELECT can be slow.

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Jun 27, 2022
    As I said before, what is your plan exactly!
    Is it about updating external data and connections? Is it about worksheet formula update?
    Is it table update?
    Is it about PQ update?
    Information about the Excel version, operating system and storage medium would be of great benefit.
    Any photos or files (without sensitive data) would also help.
    Please keep in mind that none of us can see your digital environment, so simple information is very useful.

    Thank you for your patience and understanding

Resources