VBA Code to Open File1; Open File2 copy sheets to File1

[Dennis]

Using XL 2003 & 97

Need help with the following VBA code:

Sub Macro1()
Dim myNewWorkbook As String
myNewWorkbook = ActiveWorkbook.Name
Workbooks.Open Filename:="OldWorkbookFile.xls"
Sheets(Array("Formulas", "Department Lables")).Activate
Sheets(Array("Formulas", "Department Lables")).Copy _
Before:=Workbooks(myNewWorkbook).Sheets(2)
Windows("OldWorkbookFile.xls").Activate
ActiveWorkbook.Close
Windows(myNewWorkbook).Activate
' ........

End Sub

My goal is to have myNewWorkbook active then:
1) Open OldWorkbookFile
2) Select two sheets in OldWorkbookFile ("Formulas", "Department Lables")
3) Copy those sheets to myNewWorkbook
4) Close OldWorkbookFile
5) Return to myNewWorkbook

Obviously I do not have the syntax correct.

TIA Dennis

 

[Dave Peterson]

Maybe...

Option Explicit
Sub Macro1A()
Dim CurWkbk As Workbook
Dim OldWkbk As Workbook

Set CurWkbk = ActiveWorkbook
Set OldWkbk = Workbooks.Open(Filename:="book2.xls")
OldWkbk.Sheets(Array("Formulas", "Department Lables")).Copy _
before:=CurWkbk.Worksheets(2)
OldWkbk.Close savechanges:=False
End Sub

One warning. Lables is usually spelled labels <bg>. If you/someone corrects
the spelling, your macro will need to be corrected, too.

 

[Dennis]

Dave, Thanks for your time.

Each time I run the routine the following line bombs with the error msg below:
(BTW I did change the spelling error on the Sheet and in the code)
I did attempt to ascertain the problem with no luck.

OldWkbk.Sheets(Array("Formulas", "Department Lables")).Copy _
before:=CurWkbk.Worksheets(2)

Error message:

Runtime error "9":
Subscript out of range

Any suggestions?

Dennis

 

[Dave Peterson]

How many worksheets do you have in the activeworkbook?

I assumed you had 2 since it was in your original code.

OldWkbk.Sheets(Array("Formulas", "Department Lables")).Copy _
before:=CurWkbk.Worksheets(1)

If you only have 1.

There are actually a couple spots that could be blowing up. Verify that you
have worksheets named "formulas" and "department Lables" in that other workbook,
too.

(maybe you corrected the spelling error????? <vbg>)

 

[Dennis]

Dave,

I have one worksheet in CurWkB
and
Two worksheets in OLdWkBk

I got the Worksheets(2) from running the macro

 

[Dennis]

Dave,

(1) worked fine.

Thanks!!

How many worksheets do you have in the activeworkbook?

I assumed you had 2 since it was in your original code.

OldWkbk.Sheets(Array("Formulas", "Department Lables")).Copy _
before:=CurWkbk.Worksheets(1)

If you only have 1.

There are actually a couple spots that could be blowing up. Verify that you
have worksheets named "formulas" and "department Lables" in that other workbook,
too.

(maybe you corrected the spelling error????? <vbg>)