Use Excel VBA to Process Worksheets in Every Workbook in a Folder
Question details
Process selected worksheets in every matching Excel workbook in a folder using VBA.

- 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.
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.
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.
In Excel, open the VBA editor with Alt+F11 or Developer > Visual Basic. Select Insert > Module.
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
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.

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.

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.




