Excel Macro

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I created a macro (using recorder) in Excel 2000 that
toggles between two workbooks (histdata.xls and test.xls)
among other things. The macro works fine when I run it
manually but bombs and gives me an error 9 "subscript out
of range" when it reaches the line "Windows
("HISTDATA.xls").Activate" when it runs automatically. I
want the macro to run automatically every time I open the
workbook so I copied the macro to the VB editor in VBA
Projects under "This Workbook" and added Workbook_open()
to run it automatically. Here is the macro:

Private Sub Workbook_Open()

Range("B5:J5").Select
Workbooks.Open Filename:="C:\HISTDATA.CSV"
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.CurrentRegion.Select
ChDir "C:\"
ActiveWorkbook.SaveAs Filename:="C:\HISTDATA.xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, _
CreateBackup:=False
Selection.Cut
Windows("test.xls").Activate
Range("B10").Select
ActiveSheet.Paste
Windows("HISTDATA.xls").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

Please help!!

Thanks

Tom
 
To

Change the line to
Workbooks("HISTDATA.xls").Activat

Ton
----- Tom wrote: ----

I created a macro (using recorder) in Excel 2000 that
toggles between two workbooks (histdata.xls and test.xls)
among other things. The macro works fine when I run it
manually but bombs and gives me an error 9 "subscript out
of range" when it reaches the line "Window
("HISTDATA.xls").Activate" when it runs automatically. I
want the macro to run automatically every time I open the
workbook so I copied the macro to the VB editor in VBA
Projects under "This Workbook" and added Workbook_open()
to run it automatically. Here is the macro

Private Sub Workbook_Open(

Range("B5:J5").Selec
Workbooks.Open Filename:="C:\HISTDATA.CSV
Rows("1:1").Selec
Selection.Delete Shift:=xlU
Range("A1").Selec
Selection.CurrentRegion.Selec
ChDir "C:\
ActiveWorkbook.SaveAs Filename:="C:\HISTDATA.xls",
FileFormat:=xlNormal,
Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False,
CreateBackup:=Fals
Selection.Cu
Windows("test.xls").Activat
Range("B10").Selec
ActiveSheet.Past
Windows("HISTDATA.xls").Activat
ActiveWorkbook.Sav
ActiveWorkbook.Clos
End Su

Please help!

Thank

To
 
Back
Top