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

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

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.