Copy Each Sheet from Closed Workbooks
In this article I will show you how to open all of the Excel files in a directory and import each worksheet into the active workbook. The procedure is a sort of batch upload of sorts for every sheet in a directory. It consolidates the sheets by first hard coding all of the data then importing the entire sheet to the active workbook. It is useful to hard code the data in the first instance so there is no external links back to the original file. This procedure is useful if you have a directory of templates and you want to consolidate them to one place.
The following is the procedure to create a new sheet with the data that relates to that sheet from a master worksheet.
Sub OpenImpShts() 'Excel VBA procedure to Copy each sheet from closed workbook.
Dim sFil As String
Dim owb As Workbook
Dim ws As Worksheet
Dim sh As Worksheet
sFil=Dir(sPath & "*.xl*")
Do While sFil <> "" 'Start the loop through Excel files.
For Each sh In ActiveWorkbook.Sheets
owb.Close False 'Close no save
It is a very simple procedure so I will not attach a file. Be sure to get the file path correct and have the extension back slash \ at the end of your file path. Be careful of the hard coded range which i have added. If your data is larger than this range you may need to accommodate for this scenario.