If you have multiple Excel workbooks that have to have the same process completed in each it can become quite laborious to complete the task manually. A quick and easy way to speed up your work processes is to do this automatically using Excel VBA.
Automatically Open Excel Workbooks in a Folder
.LookIn = "ExcelGeek\VBA\Learning\" – This specifies the folder you want to search within, including the path
.FileName = ".xls" – The type of files you wish to open, here I have specified Excel by using the .xls. If you want Word, then type .doc.
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
Set newworkbook = ActiveWorkbook
For i = 1 To .FoundFiles.Count – This can be used to count the number of files that Excel finds and opens. For each one opened the variable i increments one.
Put your code here
Next i – Open next workbook
So using the above piece of code you quickly and easily perform a routine across multiple workbooks. Just insert the code you want to repeat across all workbooks where it says “Put your code here”. Hey presto!