Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Macro Help
Hi I am trying to write a Macro that opens another workbook (test.csv) and
copies the data from and places into the workbook with the macro (testenabled.xmls) which will run on a scheduled task. I keep running into this error run time error '9' subscript out of range Here is the code - I would really appreciate some help. Sub test() ' ' test Macro ' ' ChDir "C:\Users\j.dibble.CTS\Desktop" Workbooks.Open Filename:="C:\Users\j.dibble.CTS\Desktop\test.csv" Range("A1:B5").Select Selection.Copy Windows("Book1").Activate ActiveSheet.Paste Columns("A:B").Select Range("B1").Activate Application.CutCopyMode = False With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("A2").Select Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:= _ "C:\Users\j.dibble.CTS\Desktop\testenabled.xls m", FileFormat:= _ xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False Application.DisplayAlerts = True End Sub |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Macro Help
Which line of code is being flagged?
-- Gary''s Student - gsnu200825 "Jon Dibble" wrote: Hi I am trying to write a Macro that opens another workbook (test.csv) and copies the data from and places into the workbook with the macro (testenabled.xmls) which will run on a scheduled task. I keep running into this error run time error '9' subscript out of range Here is the code - I would really appreciate some help. Sub test() ' ' test Macro ' ' ChDir "C:\Users\j.dibble.CTS\Desktop" Workbooks.Open Filename:="C:\Users\j.dibble.CTS\Desktop\test.csv" Range("A1:B5").Select Selection.Copy Windows("Book1").Activate ActiveSheet.Paste Columns("A:B").Select Range("B1").Activate Application.CutCopyMode = False With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("A2").Select Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:= _ "C:\Users\j.dibble.CTS\Desktop\testenabled.xls m", FileFormat:= _ xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False Application.DisplayAlerts = True End Sub |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Macro Help
Error 9 usually occurs when you try to refer to a file
directory/cell/destination that doesn't exist. Your code looks good, and runs fine (was able to check eveyrthing except your specific file directory callings) You could try using the debugger (pressing F8) to try and figure out which exact line is causing you problems, but I suspect one of the two file destinations is incorrect. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Jon Dibble" wrote: Hi I am trying to write a Macro that opens another workbook (test.csv) and copies the data from and places into the workbook with the macro (testenabled.xmls) which will run on a scheduled task. I keep running into this error run time error '9' subscript out of range Here is the code - I would really appreciate some help. Sub test() ' ' test Macro ' ' ChDir "C:\Users\j.dibble.CTS\Desktop" Workbooks.Open Filename:="C:\Users\j.dibble.CTS\Desktop\test.csv" Range("A1:B5").Select Selection.Copy Windows("Book1").Activate ActiveSheet.Paste Columns("A:B").Select Range("B1").Activate Application.CutCopyMode = False With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("A2").Select Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:= _ "C:\Users\j.dibble.CTS\Desktop\testenabled.xls m", FileFormat:= _ xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False Application.DisplayAlerts = True End Sub |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Macro Help
Hi Jon, did you record this macro?
Instead of opening the "testenabled" book, your code is looking for "book1" if this is not open it will error out. backup your files and try: Workbooks.Open Filename:="C:\Users\j.dibble.CTS\Desktop\test.csv" Range("A1:B5").Select Selection.Copy Workbooks.Open "C:\Users\j.dibble.CTS\Desktop\testenabled.xls m" Windows("testenabled.xlsm").Activate Range("A1").Select ActiveSheet.Paste Columns("A:B").Select Application.CutCopyMode = False With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("A2").Select Application.DisplayAlerts = False ActiveWorkbook.Save Application.DisplayAlerts = True "Jon Dibble" wrote: Hi I am trying to write a Macro that opens another workbook (test.csv) and copies the data from and places into the workbook with the macro (testenabled.xmls) which will run on a scheduled task. I keep running into this error run time error '9' subscript out of range Here is the code - I would really appreciate some help. Sub test() ' ' test Macro ' ' ChDir "C:\Users\j.dibble.CTS\Desktop" Workbooks.Open Filename:="C:\Users\j.dibble.CTS\Desktop\test.csv" Range("A1:B5").Select Selection.Copy Windows("Book1").Activate ActiveSheet.Paste Columns("A:B").Select Range("B1").Activate Application.CutCopyMode = False With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("A2").Select Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:= _ "C:\Users\j.dibble.CTS\Desktop\testenabled.xls m", FileFormat:= _ xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False Application.DisplayAlerts = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help to update macro to office 2007 macro enabled workbook | Excel Discussion (Misc queries) | |||
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) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) |