Copy Worksheet

[otacustes]

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

[Bob Phillips]

You don't need to activate it, opening it makes it active.

[otacustes]

You don't need to activate it, opening it makes it active.

Even if I comment this out, it still stops with the same error on the next line down:

Workbooks(NewExcelBook).Sheets("Sheet1").Name = "Import"

I know that there is a Sheet1 so I cannot understand why I get the same message!!

Thanks

[Bob Phillips]

That would suggest to me that ExcelName contains the full path of the workbbok, but you refer to a workbook by its name, not its full name

E.g. Mybook.xls, not C:\library\2008\MyBook.xls.

The best way is to set a workbook object when opening, and use that



'Get the active workbook name
ExistingExcelBook = ActiveWorkbook.FullName

Set wb = Workbooks.Open(NewExcelBook)
With wb
.Sheets("Sheet1").Name = "Import"
.Worksheets("Import").Visible = True
.Sheets("Import").Select
.Sheets("Import").Activate
.Sheets("Import").Copy After:=Workbooks(ExistingExcelBook).Sheets("Sheet2")
End With