Run 46 macros in one module

[meirm]

I built a file which copies the same range from 46 different spreadsheets in one file to a second file with identicle spreadsheets. The macros are all sitting ib the same module. I'm looking for a macro to run them by refering to the module they're located in rather then having to code 46 calls.

 

[jolivanes]

That should be attainable with just a single macro but we need more info.
Just to be on the same page.
Are the 46 sheets all the sheets in the workbook or are there more sheets in that same workbook (file)
Are both workbooks (files) open?
If not, where are they located?

 

[meirm]

1. Both the source and the destination workbooks are open.
2. Both workbooks have other worksheets.
3. I wrote each individual macro using the sheet name which is identicle in both worbooks opting this way rather then referring to sheet number.
4. Each sheet name is unique (referring to a different department)

 

[jolivanes]

Sub From_One_Book_To_Another_Book()
Dim wb1 As Workbook, wb2 As Workbook
Dim shArr
Dim i As Long
Application.ScreenUpdating = False
shArr = Array("Sheet2", "Sheet4", "Sheet6", "Sheet8")    '<----- Put all sheet names here
Set wb1 = ThisWorkbook    '<---- wb to copy from
On Error Resume Next
Set wb2 = Workbooks("C:\Folder Name\Name of Workbook where you paste into.xlsm")    '<---- Change to actual wb to paste into
    If Err Then Set wb2 = Workbooks.Open("C:\Folder Name\Name of Workbook where you paste into.xlsm")    '<---- Change to actual wb to paste into
On Error GoTo 0
    For i = LBound(shArr) To UBound(shArr)
        wb1.Worksheets(shArr(i)).UsedRange.Copy
        wb2.Worksheets(shArr(i)).Cells(wb2.Worksheets(shArr(i)).Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
    Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Or put all the sheet names in another sheet and hide that sheet.
Refer to that sheet and cells instead of the array.
Sounds to me that these two options are a lot easier to maintain/adapt/change than 46 macros.
If your sheets that you copy from and paste into have a particular naming that is different from the other sheets, you can use that without having to put all the sheet names in an array.

 

[meirm]

Thanks. Im out of my office but I can't wait to try it and will update how it works.

 

[jolivanes]

Set wb2 = Workbooks("C:\Folder Name\Name of Workbook where you paste into.xlsm")    '<---- Change to actual wb to paste into

Change the above line to (does not require full path here)

Set wb2 = Workbooks("Name of Workbook where you paste into.xlsm")    '<---- Change to actual wb to paste into

 

[meirm]

Thank you...
I actually have a sheet with a list of all the macros by name. What would the code look like if i referred to that sheet and range of the list.

 

[jolivanes]

If you have these 3 macros in Module2 of a Workbook named "ABCDEF.xlsm" and that workbook is open also

Sub Say_Hello()
    MsgBox "Hello"
End Sub

Sub No_Dice()
    MsgBox "No Way Jose"
End Sub

Sub Weather()
    MsgBox "It's Raining Cats And Dogs"
End Sub

In the other workbook, which will be the ActiveWorkbook, you have this code in a module

Sub Run_Macros_From_Macro()
Dim i As Long
    For i = 1 To Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, 1).End(xlUp).Row
        Application.Run "'" & "ABCDEF.xlsm" & "'!VBAProject." & "Module2." & ThisWorkbook.Worksheets("Sheet1").Range("A" & i).Value
    Next i
End Sub

In Sheet1 of this same workbook you have
in Cell A1 "Say_Hello" (without quotation marks)
in Cell A2 "No_Dice" (without quotation marks)
in Cell A3 "Weather" (without quotation marks)

Now you run the "Run_Macros_From_Macro" macro.

 

[meirm]

Hi! a clarification on the original code.
I wasn't sure if this part of the code, shArr = Array("Sheet2", "Sheet4", "Sheet6", "Sheet8") '<----- Put all sheet names here , requires that I list here all 46 worksheet names?
If this is the case, wouldn't it just be easier to call each of the 46 macros that I already have?

 

[meirm]

Thank you!.This is the VBA code I am looking for. I ran your example and it ran great. When I tried to adapt it to my code I got a runtime 1004 error

This is how I adapted your code:

Sub Run_Macros_From_Macro()
Dim i As Long
For i = 1 To Sheets("MacroList").Cells(Sheets("MacroList").Rows.Count, 1).End(xlUp).Row
Application.Run "'" & "Destination.xlsm" & "'!VBAProject." & "Module3." & ThisWorkbook.Worksheets("MacroList").Range("A" & i).Value
Next i
End Sub

All my VBA macros are in Module3 of my Destination.xlsm workbook.
My list of macro is also in that workbook in the worksheet named MacroList.
The range of that list starts in cell A1:A49.
I placed this macro in Module2 of the same workbook.
As you probably already guessed, I am a newbie when it comes to VBA but I am working on upgrading my knowledge.