Put all 9 workbooks in a folder making sure they are the only XLS file in the
folder. Then change the name of the Folder in the macro below to match the
folder where the files are lcoated.

Sub combinebooks()

Folder = "c:\temp\"

FName = Dir(Folder & "*.xls")

First = True 'used to indicate when first workbook is added
Do While FName < ""
Set oldbk = Workbooks.Open(Filename:=Folder & FName)
For Each sht In oldbk.Sheets
If First = True Then
'simply copy the worksheets to thisworkbook
With ThisWorkbook
sht.Copy after:=.Sheets(.Sheets.Count)
End With
Else
LastRow = sht.Range("A" & Rows.Count).End(xlUp).Row
sht.Rows("1:" & LastRow).Copy
With ThisWorkbook.Sheets(sht.Name)
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
If .Range("A1") = "" Then
'if sheet is empty
NewRow = 1
Else
'if sheet is not empty
NewRow = LastRow + 1
End If
.Rows(NewRow).Paste
End With
End If
Next
First = False
oldbk.Close savechanges:=False
FName = Dir()
Loop
End Sub


"David W. Owens" wrote:

I was given 9 Excel files, each containing 27 sheets (A to Z and Memo). Each
sheet contains 3 cols: Name, Date, Page.
Example: Sheet named €œA€�, contains people whose name begins with an €œA€� and
has a data and a page number. Sheet named €œB€�, contains people whose name
begins with a €œB€� and has a data and a page number. And so on. I want to
combine all the names in the €œA€� Sheet. The Memo Sheet will be ignored.
This is the first step for 9 files.

Each of the 9 files is a decade of 10 years, and I want to combine all the
Names a 10 year file, then combine all the files into one file.

Cut and Paste is slow for 27 sheets, and doesnt work.