VBA & Macro Problems

Use Excel VBA to Process Worksheets in Every Workbook in a Folder

John WilsonJohn Wilson Nov 20, 2026 870 views

Question details

Process selected worksheets in every matching Excel workbook in a folder using VBA.

Use Excel VBA to Process Worksheets in Every Workbook in a Folder
Product
Microsoft Excel
Device & OS
not provided
Scenario
A VBA macro needs to open each macro-enabled workbook in a folder and process worksheets whose names begin with Forecast.
Observed behavior
The existing VBA code processes only the first workbook in the folder.
Before you start

Identify the folder containing the workbooks and confirm that .xlsm files and worksheet names beginning with Forecast are the intended targets. Open Excel's VBA editor and choose a standard module for the macro.

Solution 1Recommended

Loop through matching workbooks with Excel VBA Dir

Use this loop to retrieve each .xlsm filename, inspect its Forecast worksheets, and move on to the next file.

The example prints matching workbook and worksheet names to the Immediate window. Replace the Debug.Print line with your processing code; if that code changes a workbook, change SaveChanges to True after testing.

Avoid calling Dir elsewhere in the processing code because it can reset the active file enumeration. The example skips workbooks that are already open.

1
Open a standard module

In Excel, open the VBA editor with Alt+F11 or Developer > Visual Basic. Select Insert > Module.

2
Add the workbook loop

Paste the following macro into the module and replace the example folder path with your own: Sub ProcessForecastSheets() Dim folderPath As String, fileName As String, fullPath As String Dim wb As Workbook, openWb As Workbook, ws As Worksheet Dim isOpen As Boolean On Error GoTo HandleError folderPath = "C:\YourFolder" If Right$(folderPath, 1) <> Application.PathSeparator Then folderPath = folderPath & Application.PathSeparator End If fileName = Dir(folderPath & "*.xlsm") Do While fileName <> "" fullPath = folderPath & fileName isOpen = False For Each openWb In Application.Workbooks If StrComp(openWb.FullName, fullPath, vbTextCompare) = 0 Then isOpen = True Exit For End If Next openWb If Not isOpen Then Set wb = Workbooks.Open(fullPath) For Each ws In wb.Worksheets If LCase$(Left$(ws.Name, 8)) = "forecast" Then Debug.Print wb.Name & " - " & ws.Name ' Add worksheet processing here. End If Next ws wb.Close SaveChanges:=False Set wb = Nothing End If fileName = Dir() Loop Exit Sub HandleError: MsgBox "Processing stopped: " & Err.Description, vbExclamation On Error Resume Next If Not wb Is Nothing Then wb.Close SaveChanges:=False End Sub

3
Run and check the results

Open View > Immediate Window in the VBA editor, place the cursor in the macro, and choose Run > Run Sub/UserForm. Check the window for matching workbook and worksheet names.

Loop through matching workbooks with Excel VBA Dir
Saving changes: The example closes processed files without saving. If your processing modifies worksheets, use SaveChanges:=True on the normal close after testing. If an error occurs, the current workbook is closed without saving.
Free Microsoft Office alternative

Work with Excel spreadsheets in WPS Office

WPS Office is a free, lightweight Office alternative with a familiar interface for opening and editing spreadsheets. Its Excel format compatibility helps make migration straightforward, though it does not fix Excel's VBA loop.

Open and organize worksheets in WPS SpreadsheetsWork with common Microsoft Excel formats, including XLSX and XLSNavigate a familiar spreadsheet interfaceUse a lightweight office suite with a free version
microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA macro process only the first workbook?

The macro may call Dir only once. Start with Dir(folderPath & "*.xlsm"), then call Dir() inside the loop after each workbook is processed to get the next matching filename.

How do I process all Excel workbooks instead of only XLSM files?

Change the pattern in the first Dir call from "*.xlsm" to "*.xls*". This can match several Excel file types, so check that every matched file is appropriate for your processing code.

How can I save changes made by the macro?

After testing your worksheet-processing code, change the normal wb.Close SaveChanges:=False statement to wb.Close SaveChanges:=True. The error handler in the example still closes the current workbook without saving.

Can WPS Office run the same VBA macro?

VBA support and compatibility vary by WPS version and configuration. Check the VBA support available in your installation and test the macro before relying on it; WPS does not repair Excel's VBA code.