Run Macros in Other Workbooks with One Button

[rfm12]

Hi,

I have a main workbook (Main.xlsm) and a bunch of subsidiary ones (Book1.xlsm, Book2.xlsm, etc.). All have the same macros in them. I'd like to create a subroutine in Main with a button that runs a given macro in all the other workbooks (*except* Main), using the macro in each workbook. All the workbooks will be open when this happens. I've tried this:

Sub RunAll()
Dim Book As Workbook
For Each Book In Workbooks
Application.Run "Macro1"
Next Book
End Sub

However, this code is in a module in Main, and what it does is run Macro1 from the subsidiary book in Main. How can I run Macro1 from each subsidiary book in that book?

Thanks.

 

[Deepak]

Hello @rfm12
Welcome here!!

I haven't tested but this should be work. Test in a dummy folder/file

Also Set a reference to Microsoft Scripting Runtime by using Tools > References in the Visual Basic Editor (Alt+F11)

Sub Refresh_wb()
Dim objFSO As New Scripting.FileSystemObject
Dim objTopFolder As Scripting.Folder
Dim objFile As Scripting.File
Dim strTopFolderName As String

'Set a reference to Microsoft Scripting Runtime by using
'Tools > References in the Visual Basic Editor (Alt+F11)

Application.ScreenUpdating = False

strTopFolderName = Application.ThisWorkbook.Path
Set objTopFolder = objFSO.GetFolder(strTopFolderName)

For Each objFile In objTopFolder.Files
    Set owbk = Workbooks.Open(objFile)
        Application.Run "'" & objFile.Name & "'!" & "Macro1"
    objFile.Close True
Next

Set objTopFolder = Nothing
Application.ScreenUpdating = True

MsgBox "Done!", vbInformation, "Done"
End Sub

 

[Deepak]

Ooops..

Change this in that code..

For Each objFile In objTopFolder.Files
If objFile.Name <> ThisWorkbook.Name Then
    Set owbk = Workbooks.Open(objFile)
        Application.Run "'" & objFile.Name & "'!" & "Macro1"
    objFile.Close True
End If
Next

 

[rfm12]

Thanks very much -- it was very kind of you to take the time to write this. When I run it, I get a compile error at objFile.Close True -- it says "Method or data member not found."

Based on some further testing, I'm not sure this can be done so easily. It appears that the code in each subsidiary book runs on the "active" workbook, i.e., Main. I've tried changing ActiveWorkbook etc. to ThisWorkbook, but it doesn't help. This is turning out to be more complex than I had thought, so please don't feel obliged to spend any more time on it.

 

[Deepak]

Thanks very much -- it was very kind of you to take the time to write this. When I run it, I get a compile error at objFile.Close True -- it says "Method or data member not found."

Based on some further testing, I'm not sure this can be done so easily. It appears that the code in each subsidiary book runs on the "active" workbook, i.e., Main. I've tried changing ActiveWorkbook etc. to ThisWorkbook, but it doesn't help. This is turning out to be more complex than I had thought, so please don't feel obliged to spend any more time on it.

Have you did this..

Also Set a reference to Microsoft Scripting Runtime by using Tools > Referencesin the Visual Basic Editor (Alt+F11)

 

[NARAYANK991]

Hi ,

Try this :

Sub Button1_Click()
    For Each wb In Workbooks
        If wb.Name <> ThisWorkbook.Name Then
          wb.Activate
          Run wb.Name & "!" & "ThisWorkbook" & "." & "Macro1"
        End If
    Next
End Sub

Narayan

 

[rfm12]

Yes, I did both of those -- thanks.

I think the problem is that the macro in the subsidiary books isn't just one subroutine -- it launches a number of them, and control is passing to the Main book. I don't think there's an easy solution without a detailed look at the code, which I may have someone do, or I'll just run them manually.