Import all sheets, without knowing their names

[Jim Thomlinson]

This should be close...

dim wbkTarget as workbook
dim wksTarget as worksheet

on error resume next
set wbktarget = workbooks("MyBook.xls")
if wbktarget is nothing then _
set wbktarget = workbooks.open("C:\MyBook.xls")
on error goto 0
if wbktarget is nothing then
msgbox "Sorry you book can not be found"
else
for each wkstarget in wbktarget.worksheets
wkstarget.copy Before:=Thisworkbook.worksheets(1)
'you can rename the sheet here also if you want
next wkstarget
end if

 

[Mike]

This will get you your sheet names
For Each shName In Worksheets
MsgBox shName.Name
Next

 

[Don Guillett]

Try this idea.

Sub importandnamesheets()
With Workbooks("yoursourceworkbookname.XLS")
For i = 1 To .Worksheets.Count
'MsgBox .Sheets(i).Name
.Sheets(i).Copy after:=Workbooks("destbookname").Sheets(i)
ActiveSheet.Name = "xxx" & i
Next i
End With
End Sub

 

[MerleSmith]

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

 

[CLR]

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

 

[Don Guillett]

Glad to help

 

[CLR]

Many thanks to all who responded.......many good suggestions there,......in
the end, Don's suggestion struck my fancy so I went with it and all is well
now.........

Thanks Don, and thanks to Mike, Merle, and Jim as well......you guys are all
fantastic.

Vaya con Dios,
Chuck, CABGx3