VBA & Macro Problems

Process Multiple Excel Workbooks in a Folder with VBA

Aamir Naveed AkramAamir Naveed Akram Nov 14, 2026 869 views

Question details

Process workbooks in a folder with an Excel VBA macro that formats column E, replaces its formulas with values, and deletes specified columns.

Process Multiple Excel Workbooks in a Folder with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
Apply the same changes to the first worksheet of each workbook in a folder.
Observed behavior
Column E should display currency amounts and contain values instead of formulas; the original columns A, J, L, and M should be deleted.
Before you start

Put copies of the workbooks in a test folder and note its full path. Check that the amounts in column E are numeric, because changing the number format does not convert text to numbers.

Solution 1Recommended

Run a VBA macro on each workbook in the folder

Use this macro to modify and save the first worksheet of each matching workbook.

The macro replaces formulas with values only in column E, then deletes the original columns from right to left. Deleting columns can affect formulas elsewhere in a workbook.

The example uses a dollar format and does not refresh external links. Change the number format if you need a different currency.

1
Open the VBA editor

Open Excel and press Alt+F11. In the VBA editor, select Insert > Module in a workbook where you can save and run the macro.

2
Insert the macro

Paste the following code into the module, then replace the folder path with your test folder path, including the final backslash: Sub ProcessWorkbooks() Const folderPath As String = "C:\MYWORKSHEETS\" Dim fileName As String Dim wb As Workbook Dim ws As Worksheet Dim cellsInE As Range Dim previousScreenUpdating As Boolean Dim errorMessage As String previousScreenUpdating = Application.ScreenUpdating On Error GoTo Failed Application.ScreenUpdating = False fileName = Dir(folderPath & "*.xls*") Do While fileName <> "" If Left$(fileName, 2) <> "~$" And _ StrComp(folderPath & fileName, ThisWorkbook.FullName, vbTextCompare) <> 0 Then Set wb = Workbooks.Open(folderPath & fileName, UpdateLinks:=0) If wb.ReadOnly Then Err.Raise vbObjectError + 1, , "Workbook is read-only: " & fileName Set ws = wb.Worksheets(1) Application.Calculate ws.Columns("E").NumberFormat = "$#,##0.00" Set cellsInE = Intersect(ws.UsedRange, ws.Columns("E")) If Not cellsInE Is Nothing Then cellsInE.Value = cellsInE.Value ws.Columns("M").Delete ws.Columns("L").Delete ws.Columns("J").Delete ws.Columns("A").Delete wb.Close SaveChanges:=True Set wb = Nothing End If fileName = Dir Loop Application.ScreenUpdating = previousScreenUpdating MsgBox "Processing complete." Exit Sub Failed: errorMessage = Err.Description On Error Resume Next If Not wb Is Nothing Then wb.Close SaveChanges:=False Application.ScreenUpdating = previousScreenUpdating MsgBox "Processing stopped: " & errorMessage End Sub

3
Run and check the test

Place the cursor inside ProcessWorkbooks and press F5. Open several processed files and check the first sheet's amounts, values, and remaining columns before using the macro on other files.

Scope and saved changes: The macro saves each successfully processed workbook and stops at the first error. Earlier files remain changed. It skips the macro workbook and Excel temporary lock files.
Free Microsoft Office alternative

Edit Excel Workbooks with WPS Spreadsheet

WPS Office is a free, lightweight Office alternative with a familiar interface for opening and editing Excel workbooks. Its format compatibility makes it easy to move existing files into WPS; VBA macro support can vary by version and setup.

Open and edit Microsoft Excel XLS and XLSX files.Format currency amounts and manage worksheet columns.Use a familiar spreadsheet interface for a smoother transition from Excel.Work with a free, lightweight Office suite.
microsoft office alternative - wps office

Frequently Asked Questions

Does the macro process every worksheet in each workbook?

No. The line Set ws = wb.Worksheets(1) selects only the first worksheet in each workbook.

Does the macro replace formulas throughout the workbook?

No. It replaces formulas with their current values only in the used portion of column E on the first worksheet.

Why are the columns deleted from right to left?

Deleting M, L, J, and then A prevents earlier deletions from shifting the positions of columns that still need to be removed.

Will formatting column E convert text into numbers?

No. The NumberFormat property changes how numeric values are displayed; numeric text must be converted separately.

What happens if a workbook cannot be processed?

The macro stops, closes the current workbook without saving its changes, and displays an error message. Workbooks processed earlier remain saved.