|
Import all sheets, without knowing their names
This will get you your sheet names
For Each shName In Worksheets MsgBox shName.Name Next "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 |
Import all sheets, without knowing their names
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 Befo=Thisworkbook.worksheets(1) 'you can rename the sheet here also if you want next wkstarget end if -- HTH... Jim Thomlinson "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 |
Import all sheets, without knowing their names
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "CLR" wrote in message ... 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 |
Import all sheets, without knowing their names
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 |
Import all sheets, without knowing their names
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 |
Import all sheets, without knowing their names
|
Import all sheets, without knowing their names
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 "CLR" wrote in message ... 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 |
| All times are GMT +1. The time now is 09:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com