Combine worksheets from different excel files into one excel file

[Guest]

I need to combine about 16 different excel files (one workbook each) into
excel file. I don't want to combine them all into one worksheet. I need to
keep them separate, but in the same workbook. They all have the same format.
Thanks in advance.

 

[Dave Peterson]

Are they all in the same folder?

If yes, then run this and select the files that should be combined:

Option Explicit
Sub testme01()

Dim myFileNames As Variant
Dim wkbk As Workbook
Dim fCtr As Long
Dim newWkbk As Workbook

myFileNames = Application.GetOpenFilename("Excel Files, *.xls", _
MultiSelect:=True)

If IsArray(myFileNames) = False Then
Exit Sub
End If

Set newWkbk = Workbooks.Add(1) 'single sheet
newWkbk.Worksheets(1).Name = "dummynamehere"

For fCtr = LBound(myFileNames) To UBound(myFileNames)
Set wkbk = Workbooks.Open(Filename:=myFileNames(fCtr))
wkbk.Worksheets(1).Copy _
after:=newWkbk.Worksheets(1)
wkbk.Close savechanges:=False
Next fCtr

Application.DisplayAlerts = False
newWkbk.Worksheets("dummynamehere").Delete
Application.DisplayAlerts = True
End Sub

 

[Doug Kanter]

I need to combine about 16 different excel files (one workbook each) into
excel file. I don't want to combine them all into one worksheet. I need to
keep them separate, but in the same workbook. They all have the same
format.
Thanks in advance.

Right click the tab at the bottom and you'll see options for copying the
sheet. You can make a copy that stays in the same workbook, or send the copy
to a new workbook, or...the one you want: Copy it into an existing workbook.
The workbook that receives the copy must be open at the time of the copy.