Sub CopySheets()

'The workbook to be copied is open before starting the macro

Dim WorkbookToBeCopied, WorkbookToPaste As String
Dim SheetCount, I As Integer

WorkbookToBeCopied = ActiveWorkbook.Name
Workbooks.Add
WorkbookToPaste = ActiveWorkbook.Name

Workbooks(WorkbookToBeCopied).Activate
SheetCount = Sheets.Count

Workbooks(WorkbookToPaste).Activate
If Sheets.Count < SheetCount Then
Sheets.Add Count:=SheetCount - Sheets.Count
End If

For I = 1 To SheetCount
Workbooks(WorkbookToBeCopied).Activate
Sheets(I).Activate
Cells.Copy
Workbooks(WorkbookToPaste).Activate
Range("A1").Select
ActiveSheet.Paste
Sheets(I).Name = "XXX" & I
Next I
End Sub
--
Merle


"CLR" wrote:

Hi All........
If someone would be so kind........I am in need of code to open a second
Excel file, and import all of the several sheets therein into the first
book. I do not know exactly how many sheets there are, nor their
names.....but there are about 6-10 sheets in the second book. Upon
importing them, I would like to assign each my own unique name, like XXX1,
XXX2, etc.

I know how to go and get sheets whose names I know, I just don't know how to
get all of them when I don't know how many there are, nor their names.

TIA for any assistance,
Vaya con Dios,
Chuck, CABGx3