Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
passing variables from an excel macro to a powerpoint macro | Excel Programming |