Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A bit bamboozeled
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A bit bamboozeled
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 In article , "Jimbola" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
A bit bamboozeled
BTW: Not sure why you're "bamboozled" - To bamboozle implies a
deliberate deception. Sounds more like you're just confused. In article , "Jimbola" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|