Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.programming
|
|||
|
|||
Macro Help
If you click "Debug" in the error message, it will hghlight the code line
that fails.The error means that the specified address isn't there. For example, if this highlights Windows("Book1").Activate it means that there is no Book1 available. HTH. Best wishes Harald "Jon Dibble" skrev i melding ... 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.programming
|
|||
|
|||
Macro Help
Try these changges
Sub test() ' ' test Macro ' ' Set NewSht = ThisWorkbook.ActiveSheet ChDir "C:\Users\j.dibble.CTS\Desktop" Set CSVFvile = Workbooks.Open(FileName:="C:\Users\j.dibble.CTS\De sktop\test.csv") CSVFile.ActiveSheet.Range("A1:B5").Copy _ Destination:=NewSht.Range("A1") Application.CutCopyMode = False With NewSht.Columns("A:B") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Application.DisplayAlerts = False ThisWorkbook.SaveAs FileName:= _ "C:\Users\j.dibble.CTS\Desktop\testenabled.xls m", _ FileFormat:=xlOpenXMLWorkbookMacroEnabled Application.DisplayAlerts = True End Sub "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.programming
|
|||
|
|||
Macro Help
Thanks Joel
This has saved me loads of time. "Joel" wrote in message ... Try these changges Sub test() ' ' test Macro ' ' Set NewSht = ThisWorkbook.ActiveSheet ChDir "C:\Users\j.dibble.CTS\Desktop" Set CSVfile = Workbooks.Open(FileName:="C:\Users\j.dibble.CTS\De sktop\test.csv") CSVFile.ActiveSheet.Range("A1:B5").Copy _ Destination:=NewSht.Range("A1") Application.CutCopyMode = False With NewSht.Columns("A:B") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Application.DisplayAlerts = False ThisWorkbook.SaveAs FileName:= _ "C:\Users\j.dibble.CTS\Desktop\testenabled.xls m", _ FileFormat:=xlOpenXMLWorkbookMacroEnabled Application.DisplayAlerts = True End Sub "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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
Joel
Have implemented this now into my actual Macro but it is falling over debug points at - Sheets("Sheet2").Select If you could help would be really ace. Sub bcms() ' ' bcms Macro ' ' Set NewSht = ThisWorkbook.ActiveSheet ChDir "Y:\" Set CSVFile = Workbooks.Open(Filename:="Y:\report_list_bcms_skil l_1_.csv") CSVFile.ActiveSheet.Range("A1:U20").Copy _ Destination:=NewSht.Range("A1") Application.CutCopyMode = False Cells.Select Cells.EntireColumn.AutoFit Columns("A:B").Select Range("B1").Activate Selection.Delete Shift:=xlToLeft Columns("B:G").Select Selection.Delete Shift:=xlToLeft Columns("C:E").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Rows("19:20").Select Selection.Delete Shift:=xlUp Range("B4:B18").Select Selection.TextToColumns Destination:=Range("B4"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _ :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True Columns("C:C").Select Selection.Delete Shift:=xlToLeft Columns("C:D").Select Selection.Delete Shift:=xlToLeft Rows("1:3").Select Range("A3").Activate Selection.Delete Shift:=xlUp Rows("1:1").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Columns("B:G").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.ColumnWidth = 10 Range("A2:A16").Select Selection.Replace What:=",", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.TextToColumns Destination:=Range("A2"), DataType:=xlFixedWidth, _ OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(8, 1), Array(13, _ 1), Array(17, 1), Array(19, 1)), TrailingMinusNumbers:=True Columns("A:C").Select Range("C1").Activate Selection.Delete Shift:=xlToLeft Range("A2").Select Sheets("Sheet2").Select ActiveCell.FormulaR1C1 = "JAN" Range("A3").Select ActiveCell.FormulaR1C1 = "FEB" Range("A3").Select Selection.ClearContents Range("A2").Select ActiveCell.FormulaR1C1 = "FEB" Range("A3").Select ActiveCell.FormulaR1C1 = "MAR" Range("A1:A3").Select Selection.AutoFill Destination:=Range("A1:A12"), Type:=xlFillDefault Range("A1:A12").Select Range("B1").Select ActiveCell.FormulaR1C1 = "1" Range("B2").Select ActiveCell.FormulaR1C1 = "2" Range("B3").Select ActiveCell.FormulaR1C1 = "3" Range("B1:B3").Select Selection.AutoFill Destination:=Range("B1:B12"), Type:=xlFillDefault Range("B1:B12").Select Sheets("Sheet1").Select Columns("A:C").Select Range("C1").Activate Selection.ColumnWidth = 8 Range("D2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet2!C[-3]:C[-2],2,FALSE)" Selection.AutoFill Destination:=Range("D2:D16"), Type:=xlFillDefault Range("D2:D16").Select Columns("D:D").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Columns("A:A").Select Selection.Delete Shift:=xlToLeft Columns("D:D").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("D2").Select ActiveCell.FormulaR1C1 = "=DATE(RC[-2],RC[-1],RC[-3])" Range("D2").Select Selection.AutoFill Destination:=Range("D2:D16") Range("D2:D16").Select Columns("D:D").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Columns("A:C").Select Range("C1").Activate Selection.Delete Shift:=xlToLeft Columns("A:M").Select Selection.ColumnWidth = 12.86 Range("A1").Select ActiveCell.FormulaR1C1 = "DATE" Range("B1").Select ActiveCell.FormulaR1C1 = "TIME" Range("C1").Select ActiveCell.FormulaR1C1 = "INBOUND" Range("D1").Select ActiveCell.FormulaR1C1 = "AVG INBOUND TIME" Range("E1").Select ActiveCell.FormulaR1C1 = "ABAND" Range("F1").Select ActiveCell.FormulaR1C1 = "AVG ABAND TIME" Range("G1").Select ActiveCell.FormulaR1C1 = "AVG TALK TIME" Columns("H:J").Select Selection.Delete Shift:=xlToLeft Range("H1").Select ActiveCell.FormulaR1C1 = "TOTAL INTERNAL" Range("I1").Select ActiveCell.FormulaR1C1 = "AVG STAFF" Range("J1").Select ActiveCell.FormulaR1C1 = "% IN SERV LEVEL" Cells.Select With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Cells.EntireColumn.AutoFit Range("A2").Select Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:="Y:\bcms_skill1.xlsm", FileFormat:= _ xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False Application.DisplayAlerts = True End Sub "Jon Dibble" wrote in message ... Thanks Joel This has saved me loads of time. "Joel" wrote in message ... Try these changges Sub test() ' ' test Macro ' ' Set NewSht = ThisWorkbook.ActiveSheet ChDir "C:\Users\j.dibble.CTS\Desktop" Set CSVfile = Workbooks.Open(FileName:="C:\Users\j.dibble.CTS\De sktop\test.csv") CSVFile.ActiveSheet.Range("A1:B5").Copy _ Destination:=NewSht.Range("A1") Application.CutCopyMode = False With NewSht.Columns("A:B") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Application.DisplayAlerts = False ThisWorkbook.SaveAs FileName:= _ "C:\Users\j.dibble.CTS\Desktop\testenabled.xls m", _ FileFormat:=xlOpenXMLWorkbookMacroEnabled Application.DisplayAlerts = True End Sub "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 | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |