CODEWITHSUNDEEP
excelvba
Arshit patel
Arshit patel

Reputation: 133

Running macro on multiple sheets at same time

I have multiple workbooks and each contains workbook open event code stored in "this workbook":

Option Explicit

Private Sub Workbook_Open()
    Call Module4.macro_timer
End Sub

code stored in module 4:

Public interval As Double

Sub macro_timer()
    interval = Now + TimeValue("00:03:00")
    Application.OnTime interval, "my_macro"
End Sub

Sub my_macro()
    Workbooks(ThisWorkbook.Name).refreshall
    Call macro_timer
End Sub

All the opened workbooks have the same code.

When I open those workbooks at same time, this code should run in parallel. When I open one workbook it works, but when I open more than one workbook it is not working at all.

Upvotes: 0

Views: 448

Answers (1)

Damian
Damian

Reputation: 5174

I did this for a colleague of mine with the same situation as yours:

Option Explicit
Sub Main()

    Dim MyPaths As Variant
    Dim MyFiles As Variant
    MyPaths = Array("C:\Users\USER\Desktop\Excel\", _
        "C:\Users\USER\Desktop\Excel\", _
        "C:\Users\USER\Desktop\Excel\", _
        "C:\Users\USER\Desktop\Excel\")
    MyFiles = Array("Tiempos.xlsx", "Tiempos.xlsx", "Tiempos.xlsx", "Tiempos.xlsx")

    Dim i As Long
    Dim MyPath As String
    Dim MyFile As String
    For i = LBound(MyPaths) To UBound(MyPaths)
        MyPath = MyPaths(i)
        MyFile = MyFiles(i)
        NewInstance MyPath, MyFile
    Next i

End Sub
Sub NewInstance(Path As String, File As String)

    Dim exApp As Excel.Application
    Set exApp = New Excel.Application

    exApp.Workbooks.Open Path & File
    exApp.Visible = True

End Sub

You only need to edit the MyPaths and MyFiles arrays with your own paths and filenames.

Upvotes: 1

Related Questions