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 |
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 |
All times are GMT +1. The time now is 02:07 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com