Run same excel macro on multiple excel files

by

Assuming that you put your files in “Files” directory relative to your master workbook your code might look like this:

Sub ProcessFiles()
    Dim Filename, Pathname As String
    Dim wb As Workbook

    Pathname = ActiveWorkbook.Path & "\Files\"
    Filename = Dir(Pathname & "*.xls")
    Do While Filename <> ""
        Set wb = Workbooks.Open(Pathname & Filename)
        DoWork wb
        wb.Close SaveChanges:=True
        Filename = Dir()
    Loop
End Sub

Sub DoWork(wb As Workbook)
    With wb
        'Do your work here
        .Worksheets(1).Range("A1").Value = "Hello World!"
    End With
End Sub

In this example DoWork() is your macro that you apply to all of your files. Make sure that you do all your processing in your macro is always in the context of the wb (currently opened workbook).

Disclaimer: all possible error handling skipped for brevity.

More Related Contents:

  1. delete Autofilter Field resullt using the header Name
  2. Somehow it’s not copying [closed]
  3. Refer to sheet using codename
  4. VBA macro that search for file in multiple subfolders
  5. VBA Macro On Timer style to run code every set number of seconds, i.e. 120 seconds
  6. So, I have 6 “master” files to then divide into 40 separate files
  7. HTML Text with tags to formatted text in an Excel cell
  8. Getting ScriptControl to work with Excel 2010 x64
  9. Application.Match gives type mismatch
  10. Count and Highlight keywords within phrases
  11. Transpose a range in VBA
  12. Get list of Excel files in a folder using VBA [duplicate]
  13. Protecting cells in Excel but allow these to be modified by VBA script
  14. Saving excel worksheet to CSV files with filename+worksheet name using VB [duplicate]
  15. Select first empty cell in column F starting from row 1. (without using offset )
  16. How to “flatten” or “collapse” a 2D Excel table into 1D?
  17. Excel VBA – QueryTable AfterRefresh function not being called after Refresh completes
  18. Excel VBA: AutoFill Multiple Cells with Formulas
  19. VBA.Find and adding a value
  20. excel vba- extract text between 2 characters
  21. Round function in Excel, worksheet function vs VBA
  22. How to continue the code on the next line in VBA
  23. Excel VBA Userform – Execute Sub when something changes
  24. Count the number of rows in another sheet [duplicate]
  25. Getting Excel to refresh data on sheet from within VBA
  26. How can I generate GUIDs in Excel?
  27. Range.Find not making a difference between January and November (February and December) in VBA Excel
  28. VBA Password Input with Cancel Function
  29. Finding first blank row, then writing to it
  30. VBA inheritance, analog of super

Leave a Comment