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 function.

 

How can i set a single VBA in the opened workbook which number of sheet is variance?

 

Thanks a lot!

excel
Macros and VBA

6 Replies

  • 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.

    • 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