combine sheets from several workbooks into one

[Amy]

I have a folder full of workbooks that I need to take the first sheet out of
and combine them all into one workbook. I need each of the worksheets to keep
the names they have in the original worksheet. Does anyone have an existing
macro that will do this? Thanks!

 

[Matthew Herbert]

Amy,

You can try something along the lines of what is below. I'm using a file
filter of *.csv, but you can change the file extension to be what you need it
to be. (Depending on how your data is setup, e.g. csv file or formatted data
with formulas, there maybe faster ways to accomplish this task).

Best,

Matthew Herbert

Sub CombineCSVFIles()
Dim varFiles As Variant
Dim lngCnt As Long
Dim Wkb As Workbook
Dim Wks As Worksheet

varFiles = Application.GetOpenFilename(FileFilter:=", *.csv",
MultiSelect:=True)

If TypeName(varFiles) = "Boolean" Then
MsgBox "No files selected."
Exit Sub
End If

Set Wkb = Workbooks.Add
For lngCnt = LBound(varFiles) To UBound(varFiles)
Set Wks = Workbooks.Open(varFiles(lngCnt)).Worksheets(1)
Wks.Copy Wkb.Worksheets(1)
Wks.Parent.Close False
Next lngCnt
End Sub