A bit bamboozeled

[Guest]

Hi there,

I've created this simple open event macro, (in ThisWorkbook of Book1) but it
fails at the indicated line and I can't figure out why. The macro works fine
from within a module. It gives the error "Script out of range", but Book1 is
definately open and was opened at the start of the macro.

Private Sub Workbook_Open()

Workbooks.Open Filename:="C:\Book1.xls"
Cells.Select
Selection.Copy
Windows("Book2.xls").Activate
Cells.Select
ActiveSheet.Paste
Columns("H:H").Select
Application.CutCopyMode = False
Selection.NumberFormat = "[$-809]dd mmmm yyyy;@"
Windows("Book1.xls").Activate <<<<<<<<<<<<<<
ActiveWindow.Close

End Sub

Any ideas?

J

 

[JE McGimpsey]

Your macro is in the ThisWorkbook code module which is a class module
for the workbook. This means that an unqualified reference to the
Windows collection will default to the active workbook, so

Windows("Book1.xls").Activate

attempts to activate the Window("Book1.xls") of Book2.xls, which gives
the "Subscript out of range" error, since it doesn't exist.

You can fix this by using

Application.Windows("Book1.xls").Activate

Or you can eliminate the activations altogether:

Private Sub Workbook_Open()
Workbooks.Open Filename:="C:\Book1.xls"
With Workbooks("Book2.xls").Sheets(1)
ActiveSheet.Cells.Copy Destination:=.Range("A1")
.Range("H:H").NumberFormat = "[$-809]dd mmmm yyyy;@"
End With
Workbooks("Book1.xls").Close
End Sub

 

[JE McGimpsey]

BTW: Not sure why you're "bamboozled" - To bamboozle implies a
deliberate deception. Sounds more like you're just confused.