Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to run a macro at same time across multiple workbook files?

This article, I will talk about how to run a macro across multiple workbook files at the same time without opening them. The following method can help you to solve this task in Excel.

Run a macro at same across multiple workbooks with VBA code


Run a macro at same across multiple workbooks with VBA code

To run a macro across multiple workbooks without opening them, please apply the following VBA code:

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following macro in the Module Window.

VBA code: Run the same macro on multiple workbooks at same time:

Sub LoopThroughFiles()
    Dim xFd As FileDialog
    Dim xFdItem As Variant
    Dim xFileName As String
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    If xFd.Show = -1 Then
        xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
        xFileName = Dir(xFdItem & "*.xls*")
        Do While xFileName <> ""
            With Workbooks.Open(xFdItem & xFileName)
                'your code here
            End With
            xFileName = Dir
        Loop
    End If
End Sub

Note: In the above code, please copy and paste your own code without the Sub heading and End Sub footer between the With Workbooks.Open(xFdItem & xFileName) and End With scripts. See screenshot:

doc run macro multiple files 1

3. Then press F5 key to execute this code, and a Browse window is displayed, please select a folder which contains the workbooks that you want to all apply this macro, see screenshot:

doc run macro multiple files 2

4. And then click OK button, the desired macro will be executed at once from one workbook to others.


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Caitlin Jarvis · 3 months ago
    Hi, firstly thank you for this macro, it was exactly what I was looking for. I do however have one problem, is there a way to close and save as each window as it completes. I have a large amount of files and I'm running out of RAM before the execution is complete.
    • To post as a guest, your comment is unpublished.
      skyyang · 3 months ago
      Hello, Caitlin ,
      Maybe the below code can help you, each time after running your specific code, a save file prompt box will pop out remind you to save the workbook.

      Sub LoopThroughFiles()
      Dim xFd As FileDialog
      Dim xFdItem As Variant
      Dim xFileName As String
      Dim xWB As Workbook
      Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
      If xFd.Show = -1 Then
      xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
      xFileName = Dir(xFdItem & "*.xls*")
      On Error Resume Next
      Do While xFileName <> ""
      Set xWB = Workbooks.Open(xFdItem & xFileName)
      With xWB
      'your code here
      End With
      xWB.Close
      xFileName = Dir
      Loop
      End If
      End Sub
    • To post as a guest, your comment is unpublished.
      Manvir Rai · 3 months ago
      Yes, Just add the below your following code if you wish it to save the file with the same name:

      'Saving the Workbook
      ActiveWorkbook.Save
  • To post as a guest, your comment is unpublished.
    Joel · 6 months ago
    Very useful macro, and it works great, but I would like to be able to select which files from that folder I want the macro to be ran on? For example I have 4 files in a folder with other excel files and I only want it ran on those 4 specific files. How can I tweak your macro to let me pick those 4 files from that folder?
    • To post as a guest, your comment is unpublished.
      skyyang · 6 months ago
      Hi, Joel,
      To trigger the same code in specific workbooks, you should apply the below code:

      Sub LoopThroughFiles()
      Dim xFd As FileDialog
      Dim xFdItem As Variant
      Dim xFileName As String
      Dim xFB As String
      With Application.FileDialog(msoFileDialogOpen)
      .AllowMultiSelect = True
      .Filters.Clear
      .Filters.Add "excel", "*.xls*"
      .Show
      If .SelectedItems.Count < 1 Then Exit Sub
      For lngCount = 1 To .SelectedItems.Count
      xFileName = .SelectedItems(lngCount)
      If xFileName <> "" Then
      With Workbooks.Open(Filename:=xFileName)
      'your code
      End With
      End If
      Next lngCount
      End With
      End Sub

      Please try it, hope it can help you!
  • To post as a guest, your comment is unpublished.
    yarto logistics · 6 months ago
    I followed instructions but get a compile error "Loop wihtout Do". What am i missing? My macro code is very simple just change font size of specified rows. Works by it self. Here is what I have... please help

    Sub LoopThroughFiles()
    Dim xFd As FileDialog
    Dim xFdItem As Variant
    Dim xFileName As String
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    If xFd.Show = -1 Then
    xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
    xFileName = Dir(xFdItem & "*.xls*")
    Do While xFileName <> ""
    With Workbooks.Open(xFdItem & xFileName)
    'your code here
    Rows("2:8").Select
    With Selection.Font
    .Name = "Arial"
    .Size = 12
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .Color = -11518420
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
    End With
    xFileName = Dir
    Loop
    End If
    End Sub
    • To post as a guest, your comment is unpublished.
      skyyang · 6 months ago
      Hello, yarto,
      You missed the "End with" script at the end of your code, the correct one should be this:
      Sub LoopThroughFiles()
      Dim xFd As FileDialog
      Dim xFdItem As Variant
      Dim xFileName As String
      Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
      If xFd.Show = -1 Then
      xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
      xFileName = Dir(xFdItem & "*.xls*")
      Do While xFileName <> ""
      With Workbooks.Open(xFdItem & xFileName)
      'your code here
      Rows("2:8").Select
      With Selection.Font
      .Name = "Arial"
      .Size = 16
      .Strikethrough = False
      .Superscript = False
      .Subscript = False
      .OutlineFont = False
      .Shadow = False
      .Underline = xlUnderlineStyleNone
      .Color = -11518420
      .TintAndShade = 0
      .ThemeFont = xlThemeFontNone
      End With
      End With
      xFileName = Dir
      Loop
      End If
      End Sub

      Please try it, hope it can help you!
  • To post as a guest, your comment is unpublished.
    Iulia Curtman · 11 months ago
    Very useful macro, and it works fine, but I would like to be able to select which files from that folder I want the macro to be ran on? The files are not generated automatically in a separate folder, and I need to run different macros on each set of files from that folder, and then move them back in the initial folder.