otacustes
07-27-2008, 11:55 AM
Hello all
I am trying to copy a sheet from another workbook into my current workbook using the following code:
'Get the active workbook name
ExistingExcelBook = ActiveWorkbook.FullName
Workbooks.Open (NewExcelBook)
Workbooks(NewExcelBook).Activate
Workbooks(NewExcelBook).Sheets("Sheet1").Name = "Import"
Workbooks(NewExcelBook).Worksheets("Import").Visible = True
Workbooks(NewExcelBook).Sheets("Import").Select
Workbooks(NewExcelBook).Sheets("Import").Activate
Workbooks(NewExcelBook).Sheets("Import").Copy After:=Workbooks(ExistingExcelBook).Sheets("Sheet2")
Unfortunately I get an error: 'Subscript out of range' when I run this and for the life of me I cannot work out why. In the past I have had this when iterating through sheets or collections.
The code stops at Workbooks(NewExcelBook).Activate
The thing is that the Workbooks.Open (NewExcelBook) code does open the workbook because it appears on the task bar below and I can look at it to see all of the data. The other thing is that there is a sheet1 so it is not like it does not exist!!
Any ideas anyone?
Any help is appreciated.
Thanks
I am trying to copy a sheet from another workbook into my current workbook using the following code:
'Get the active workbook name
ExistingExcelBook = ActiveWorkbook.FullName
Workbooks.Open (NewExcelBook)
Workbooks(NewExcelBook).Activate
Workbooks(NewExcelBook).Sheets("Sheet1").Name = "Import"
Workbooks(NewExcelBook).Worksheets("Import").Visible = True
Workbooks(NewExcelBook).Sheets("Import").Select
Workbooks(NewExcelBook).Sheets("Import").Activate
Workbooks(NewExcelBook).Sheets("Import").Copy After:=Workbooks(ExistingExcelBook).Sheets("Sheet2")
Unfortunately I get an error: 'Subscript out of range' when I run this and for the life of me I cannot work out why. In the past I have had this when iterating through sheets or collections.
The code stops at Workbooks(NewExcelBook).Activate
The thing is that the Workbooks.Open (NewExcelBook) code does open the workbook because it appears on the task bar below and I can look at it to see all of the data. The other thing is that there is a sheet1 so it is not like it does not exist!!
Any ideas anyone?
Any help is appreciated.
Thanks