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
  • mtarler's avatar
    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's avatar
      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

       

Resources