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.  

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.