Macro Help

[Jon Dibble]

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.xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Application.DisplayAlerts = True

End Sub

 

[Gary''s Student]

Which line of code is being flagged?

 

[Luke M]

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.

 

[stumac]

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.xlsm"
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