top of page

Excel VBA Code to Run Macro on Multiple Excel Files


wigi from Brussels has posted some very simple VBA code here, which can be used to run a macro on multiple other Excel files.

Sub LoopThroughFiles() FolderName = "C:\Folder1\" If Right(FolderName, 1) <> Application.PathSeparator Then FolderName = FolderName & Application.PathSeparator Fname = Dir(FolderName & "*.xls")

'loop through the files Do While Len(Fname)

With Workbooks.Open(FolderName & Fname)

' here comes the code for the operations on every file the code finds

End With

' go to the next file in the folder Fname = Dir

Loop End Sub

In Tonight's example I'm going to show how this can be used to run a macro which will split a workbook into individual worksheets, which was the Tip of the Night for April 11, 2016.

We start with a folder of multiple Excel files, some of which have more than worksheet.

Enter the vba code in a new module. On the line beginning FolderName list the path for the folder containing the Excel files you need to process.

Enter the body (without the beginning Sub heading and the End Sub footer) of the VBA code you want to run on multiple files after the comment line beginning: 'here come the code . . . and before where it reads, 'End With'.

The combine VBA codes process all of the files creating multiple .csv files with the data from each source worksheet.


Sean O'Shea has more than 20 years of experience in the litigation support field with major law firms in New York and San Francisco.   He is an ACEDS Certified eDiscovery Specialist and a Relativity Certified Administrator.

The views expressed in this blog are those of the owner and do not reflect the views or opinions of the owner’s employer.

If you have a question or comment about this blog, please make a submission using the form to the right. 

Your details were sent successfully!

© 2015 by Sean O'Shea . Proudly created with Wix.com

bottom of page