Running multiple macros in order of multiple excel workbooks - vba
I have several excel workbooks each representing the data of the day, each workbook contains multiple sheets representing each event of the day.
I need to run 6 macros for each sheet in a workbook and then move on to the next workbook (all books are in the same folder on my desktop)
at the moment im using this (below) to run macros in order on all sheets, but they have a hard time finding something to run across all the books
Sub RUN_FILL()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
sh.Activate
Call macro_1
Call macro_2
Call macro_3
Call macro_4
Call macro_5
Call macro_6
Next sh
End Sub
any idea how i can do this?
source to share
I don't have your macros, so I created dummy macros that output some values to the Immediate window for every sheet of every workbook (except the workbook containing the macro).
The code seems to depend on an output macro that activates each worksheet. This is bad practice. I am passing workbook and worksheet name to macros. I am outputting the value of cell A1 ( .Cells(1, 1).Value
) to show how this is done.
Hope this is enough to get you started. Ask if something is unclear.
Option Explicit
Sub ControlCall()
Dim FileNameCrnt As String
Dim InxWSheet As Long
Dim MsgErr As String
Dim PathCrnt As String
Dim RowReportCrnt As Long
Dim WBookCtrl As Workbook
Dim WBookOther As Workbook
Dim WSheetNameOtherCrnt As String
If Workbooks.Count > 1 Then
' It is easy to get into a muddle if there are multiple workbooks
' open at the start of a macro like this. Avoid the problem.
Call MsgBox("Please close all other workbooks " & _
"before running this macro", vbOKOnly)
Exit Sub
End If
Application.ScreenUpdating = False
Set WBookCtrl = ActiveWorkbook
' Assume all the workbooks to be processed are in the
' same folder as the workbook containing this macro.
PathCrnt = WBookCtrl.Path
' Add a slash at the end of the path if needed.
If Right(PathCrnt, 1) <> "\" Then
PathCrnt = PathCrnt & "\"
End If
FileNameCrnt = Dir$(PathCrnt & "*.xl*")
Do While FileNameCrnt <> ""
If FileNameCrnt <> WBookCtrl.Name Then
' Consider all workbooks except the one containing this macro
Set WBookOther = Workbooks.Open(PathCrnt & FileNameCrnt)
For InxWSheet = 1 To WBookOther.Worksheets.Count
WSheetNameOtherCrnt = WBookOther.Worksheets(InxWSheet).Name
Call macro_1(WBookOther, WSheetNameOtherCrnt)
Call macro_2(WBookOther, WSheetNameOtherCrnt)
Call macro_3(WBookOther, WSheetNameOtherCrnt)
Call macro_4(WBookOther, WSheetNameOtherCrnt)
Call macro_5(WBookOther, WSheetNameOtherCrnt)
Call macro_6(WBookOther, WSheetNameOtherCrnt)
Next
WBookOther.Close SaveChanges:=False
End If
FileNameCrnt = Dir$()
Loop
Application.ScreenUpdating = True
End Sub
Sub macro_1(WBookOther As Workbook, WSheetNameOtherCrnt As String)
With WBookOther
With .Worksheets(WSheetNameOtherCrnt)
Debug.Print "1 " & WBookOther.Name & " " & _
WSheetNameOtherCrnt & " " & .Cells(1, 1).Value
End With
End With
End Sub
Sub macro_2(WBookOther As Workbook, WSheetNameOtherCrnt As String)
With WBookOther
With .Worksheets(WSheetNameOtherCrnt)
Debug.Print "2 " & WBookOther.Name & " " & _
WSheetNameOtherCrnt & " " & .Cells(1, 1).Value
End With
End With
End Sub
Sub macro_3(WBookOther As Workbook, WSheetNameOtherCrnt As String)
With WBookOther
With .Worksheets(WSheetNameOtherCrnt)
Debug.Print "3 " & WBookOther.Name & " " & _
WSheetNameOtherCrnt & " " & .Cells(1, 1).Value
End With
End With
End Sub
Sub macro_4(WBookOther As Workbook, WSheetNameOtherCrnt As String)
With WBookOther
With .Worksheets(WSheetNameOtherCrnt)
Debug.Print "4 " & WBookOther.Name & " " & _
WSheetNameOtherCrnt & " " & .Cells(1, 1).Value
End With
End With
End Sub
Sub macro_5(WBookOther As Workbook, WSheetNameOtherCrnt As String)
With WBookOther
With .Worksheets(WSheetNameOtherCrnt)
Debug.Print "5 " & WBookOther.Name & " " & _
WSheetNameOtherCrnt & " " & .Cells(1, 1).Value
End With
End With
End Sub
Sub macro_6(WBookOther As Workbook, WSheetNameOtherCrnt As String)
With WBookOther
With .Worksheets(WSheetNameOtherCrnt)
Debug.Print "6 " & WBookOther.Name & " " & _
WSheetNameOtherCrnt & " " & .Cells(1, 1).Value
End With
End With
End Sub
source to share