Complie Syntax Error

J

Jon Dibble

Hi
Am very new to VB and am having trouble with this Macro. I f anyone could
help it would be really appreciated.

Sub bcms()
'
'
'

Set NewSht = ThisWorkbook.ActiveSheet
ChDir "Y:\"
Set CSVFile =
Workbooks.Open(Filename:="Y:\report_list_bcms_skill_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
 
S

Shasur

Hi

I have added error handling code to ur code and formatted a bit. Try the
following and check the line where error is thrown

Sub bcms1()
'
'
'

On Error GoTo Err_Trap

Set NewSht = ThisWorkbook.ActiveSheet


ChDir "y:\"
Set CSVFile = Workbooks.Open(Filename:="y:\report_list_bcms_skill_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

Exit Sub
Err_Trap:
Debug.Assert Err <> 0
Err.Clear
Resume Next
End Sub


Cheers
Shasur

--
http://vbadud.blogspot.com


Jon Dibble said:
Hi
Am very new to VB and am having trouble with this Macro. I f anyone could
help it would be really appreciated.

Sub bcms()
'
'
'

Set NewSht = ThisWorkbook.ActiveSheet
ChDir "Y:\"
Set CSVFile =
Workbooks.Open(Filename:="Y:\report_list_bcms_skill_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
Click to expand...
 
J

Jon Dibble

It highlights the following code

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

Then

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

Then

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

And finally!!!

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


Shasur said:
Hi

I have added error handling code to ur code and formatted a bit. Try the
following and check the line where error is thrown

Sub bcms1()
'
'
'

On Error GoTo Err_Trap

Set NewSht = ThisWorkbook.ActiveSheet


ChDir "y:\"
Set CSVFile =
Workbooks.Open(Filename:="y:\report_list_bcms_skill_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

Exit Sub
Err_Trap:
Debug.Assert Err <> 0
Err.Clear
Resume Next
End Sub


Cheers
Shasur

--
http://vbadud.blogspot.com


Jon Dibble said:
Hi
Am very new to VB and am having trouble with this Macro. I f anyone could
help it would be really appreciated.

Sub bcms()
'
'
'

Set NewSht = ThisWorkbook.ActiveSheet
ChDir "Y:\"
Set CSVFile =
Workbooks.Open(Filename:="Y:\report_list_bcms_skill_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
Click to expand...
Click to expand...
 
B

Bob Phillips

Ithin k it is this line


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

change it to

Range("B4:B18").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

--
__________________________________
HTH

Bob

Jon Dibble said:
Hi
Am very new to VB and am having trouble with this Macro. I f anyone could
help it would be really appreciated.

Sub bcms()
'
'
'

Set NewSht = ThisWorkbook.ActiveSheet
ChDir "Y:\"
Set CSVFile =
Workbooks.Open(Filename:="Y:\report_list_bcms_skill_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
Click to expand...
 
J

Jon Dibble

Thanks Bob thats fixed the first code error

But still having issues with

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

And

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

And

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False





Bob Phillips said:
Ithin k it is this line


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

change it to

Range("B4:B18").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

--
__________________________________
HTH

Bob

Jon Dibble said:
Hi
Am very new to VB and am having trouble with this Macro. I f anyone could
help it would be really appreciated.

Sub bcms()
'
'
'

Set NewSht = ThisWorkbook.ActiveSheet
ChDir "Y:\"
Set CSVFile =
Workbooks.Open(Filename:="Y:\report_list_bcms_skill_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
Click to expand...
Click to expand...
 
P

Per Jessen

Hi
I think the problem is that CSVFile is the active workbook, which you
don't want to manipulate (I guess!)

If I'm right add the line below after: Application.CutCopyMode =
False

CSVFile.close

The highlighted statements are due to word wrap in your news reader.
The statement shall be on one line or insert space and _ at the end of
each line ( not the last). The first statement can look like this:

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


If that doesn't help, we need more information on what your trouble
is.

Regards,
Per


It highlights the following code

        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

Then

    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

Then

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
        :=False, Transpose:=False

And finally!!!

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
        :=False, Transpose:=False




Hi
Click to expand...
I have added error handling code to ur code and formatted a bit. Try the
following and check the line where error is thrown
Click to expand...
Sub bcms1()
'
'
'
Click to expand...
On Error GoTo Err_Trap
Click to expand...
Set NewSht = ThisWorkbook.ActiveSheet
Click to expand...
   ChDir "y:\"
   Set CSVFile =
Workbooks.Open(Filename:="y:\report_list_bcms_skill_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
Click to expand...
       Exit Sub
Err_Trap:
   Debug.Assert Err <> 0
       Err.Clear
       Resume Next
End Sub
Cheers
Shasur
Click to expand...

Hi
Am very new to VB and am having trouble with this Macro. I f anyone could
help it would be really appreciated.
Sub bcms()
'
'
'
Set NewSht = ThisWorkbook.ActiveSheet
    ChDir "Y:\"
    Set CSVFile =
Workbooks.Open(Filename:="Y:\report_list_bcms_skill_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:=Fa­lse,
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"),
Click to expand...
Click to expand...

...

læs mere »- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -
Click to expand...
 
B

Bob Phillips

Try re-formatting

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

And

Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone,
SkipBlanks:=False, _
Transpose:=False

And

Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone,
SkipBlanks:=False, _
Transpose:=False


--
__________________________________
HTH

Bob

Jon Dibble said:
Thanks Bob thats fixed the first code error

But still having issues with

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

And

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

And

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False





Bob Phillips said:
Ithin k it is this line


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

change it to

Range("B4:B18").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

--
__________________________________
HTH

Bob

Jon Dibble said:
Hi
Am very new to VB and am having trouble with this Macro. I f anyone
could help it would be really appreciated.

Sub bcms()
'
'
'

Set NewSht = ThisWorkbook.ActiveSheet
ChDir "Y:\"
Set CSVFile =
Workbooks.Open(Filename:="Y:\report_list_bcms_skill_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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro should Save and Overwrite file 1
Macro not returning correct data 4
Different Results from the Same Macro 2
criteriarange not equal to 4
variable number of rows. 2
excel macro missing worksheets 9
Open a GroupWise .NAB File 6
fixing a run-time error'9': Subscript out of range 0

Top