Copy Each Sheet from Closed Workbooks

Copying sheets from closed workbooks and consolidating them into the workbook which is active is a very useful task. The consolidating of 1 or many workbooks can be time consuming and having a VBA procedure to copy all of the sheets in a closed workbook can save time and save mistakes as the program will not get tired or bored. The VBA copy procedure will do the same thing over and over again.

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.  

Option Explicit

Sub OpenImpShts() 'Excel VBA procedure to Copy each sheet from closed workbook.
Const sPath="D:\Temp\" 'Change to suit
Dim sFil As String
Dim owb As Workbook
Dim ws As Worksheet
Dim sh As Worksheet

Set ws=Sheet1
sFil=Dir(sPath & "*.xl*")

Do While sFil <> "" 'Start the loop through Excel files.
Set owb=Workbooks.Open(sPath & sFil)
For Each sh In ActiveWorkbook.Sheets
sh.[A2:AZ2000] = sh.[A2:AZ2000].Value
sh.Copy After: = ws
owb.Close False 'Close no save
Next sh
sFil=Dir
Loop
End Sub

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.