Macro not returning correct data

J

Jon Dibble

Hi This Macro

Manipluates the data from a csv file then saves itself in a location ready
to be imported into SQL. The finished table looks like this - note that the
DATE column has returned #N/A. When this was recorded it returned a DATE.
Can anyone help or suggest if I could go about this task in an easier
fashion?

DATE TIME INBOUND AVG INBOUND TIME ABAND AVG ABAND TIME AVG TALK TIME
TOTAL INTERNAL AVG STAFF % IN SERV LEVEL
#N/A 08:00 11 00:05 0 00:00 01:07 258:52:00 7 100
#N/A 09:00 76 00:07 0 00:00 01:13 367:35:00 11 82
#N/A 10:00 61 00:07 2 00:03 01:18 383:06:00 11.8 76
#N/A 11:00 91 00:07 1 00:05 01:35 337:08:00 11 85
#N/A 12:00 70 00:06 1 00:09 01:56 337:54:00 11.1 87
#N/A 13:00 44 00:10 1 00:23 01:40 539:20:00 12 67
#N/A 14:00 85 00:08 1 00:10 01:42 413:56:00 12 71
#N/A 15:00 66 00:07 3 00:06 01:46 360:15:00 11 80
#N/A 16:00 61 00:07 0 00:00 01:50 397:56:00 10.7 84
#N/A 17:00 8 00:07 0 00:00 02:15 144:50:00 3.7 75
#N/A 18:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 19:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 20:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 21:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 22:00 0 00:00 0 00:00 00:00 00:00 0




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
CSVFile.Close
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").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("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("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
 
J

Joel

Most of your code is formating which cannot be eliminate or made easier. You
don't need to use the select method liike in your original code. The
Selection method takes longer to run and makes the code harder to understand
and longer to write. I made changes the way I would write the code. Compare
the differences.


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
CSVFile.Close
With Sheets("Sheet1")
.Cells.EntireColumn.AutoFit
.Columns("A:B").Delete
.Columns("B:G").Delete
.Columns("C:E").Select.Insert CopyOrigin:=xlFormatFromLeftOrAbove
.Rows("19:20").Delete Shift:=xlUp
.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

.Columns("C:C").Delete
.Columns("C:D").Delete
.Rows("1:3").Delete
.Rows("1:1").Insert CopyOrigin:=xlFormatFromLeftOrAbove
.Columns("B:G").Insert CopyOrigin:=xlFormatFromLeftOrAbove
.Columns("B:G").ColumnWidth = 10
.Range("A2:A16").Replace What:=",", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

.Range("A2:A16").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("A:C").Delete
End With

With Sheets("Sheet2")
.Range("A1") = "JAN"
.Range("A2") = "FEB"
.Range("A3") = "MAR"
.Range("A1:A3").AutoFill _
Destination:=.Range("A1:A12"), _
Type:=xlFillDefault

.Range("B1") = "1"
.Range("B2") = "2"
.Range("B3") = "3"
.Range("B1:B3").AutoFill _
Destination:=.Range("B1:B12"), _
Type:=xlFillDefault
End With

With Sheets("Sheet1")
.Columns("A:C").ColumnWidth = 8
Range("D2").FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet2!C[-3]:C[-2],2,FALSE)"
.Range("D2").Copy _
Destination:=.Range("D2:D16")

.Columns("D:D").Copy
.Columns("D:D").PasteSpecial _
Paste:=xlPasteValues

.Columns("A:A").Delete
.Columns("D:D").Insert
.Range("D2").FormulaR1C1 = "=DATE(RC[-2],RC[-1],RC[-3])"
.Range("D2").Copy _
Destination:=.Range("D2:D16")
.Columns("D:D").Copy
.Columns("D:D").PasteSpecial _
Paste:=xlPasteValues

.Columns("A:C").Delete
.Columns("A:M").ColumnWidth = 12.86
.Range("A1") = "DATE"
.Range("B1") = "TIME"
.Range("C1") = "INBOUND"
.Range("D1") = "AVG INBOUND TIME"
.Range("E1") = "ABAND"
.Range("F1") = "AVG ABAND TIME"
.Range("G1") = "AVG TALK TIME"

.Columns("H:J").Delete
.Range("H1") = "TOTAL INTERNAL"
.Range("I1") = "AVG STAFF"
Range("J1") = "% IN SERV LEVEL"

With Cells
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.EntireColumn.AutoFit
End With
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs _
Filename:="Y:\bcms_skill1.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
Application.DisplayAlerts = True
End With
End Sub

Jon Dibble said:
Hi This Macro

Manipluates the data from a csv file then saves itself in a location ready
to be imported into SQL. The finished table looks like this - note that the
DATE column has returned #N/A. When this was recorded it returned a DATE.
Can anyone help or suggest if I could go about this task in an easier
fashion?

DATE TIME INBOUND AVG INBOUND TIME ABAND AVG ABAND TIME AVG TALK TIME
TOTAL INTERNAL AVG STAFF % IN SERV LEVEL
#N/A 08:00 11 00:05 0 00:00 01:07 258:52:00 7 100
#N/A 09:00 76 00:07 0 00:00 01:13 367:35:00 11 82
#N/A 10:00 61 00:07 2 00:03 01:18 383:06:00 11.8 76
#N/A 11:00 91 00:07 1 00:05 01:35 337:08:00 11 85
#N/A 12:00 70 00:06 1 00:09 01:56 337:54:00 11.1 87
#N/A 13:00 44 00:10 1 00:23 01:40 539:20:00 12 67
#N/A 14:00 85 00:08 1 00:10 01:42 413:56:00 12 71
#N/A 15:00 66 00:07 3 00:06 01:46 360:15:00 11 80
#N/A 16:00 61 00:07 0 00:00 01:50 397:56:00 10.7 84
#N/A 17:00 8 00:07 0 00:00 02:15 144:50:00 3.7 75
#N/A 18:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 19:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 20:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 21:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 22:00 0 00:00 0 00:00 00:00 00:00 0




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
CSVFile.Close
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").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("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("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

Joel

To correct the data I need to see a sample of the original CSV file. OPen
the CSV file with NOTEPAD and copy a few of the rows onto the posting. I
also need to know what coutry youo are processing the data from. Dates in
excel expect different formats depending on the courty. If you are in
England and the Data comes from the US the Month and Day are reversed which
can cause errors like the one you are seeing.

Joel said:
Most of your code is formating which cannot be eliminate or made easier. You
don't need to use the select method liike in your original code. The
Selection method takes longer to run and makes the code harder to understand
and longer to write. I made changes the way I would write the code. Compare
the differences.


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
CSVFile.Close
With Sheets("Sheet1")
.Cells.EntireColumn.AutoFit
.Columns("A:B").Delete
.Columns("B:G").Delete
.Columns("C:E").Select.Insert CopyOrigin:=xlFormatFromLeftOrAbove
.Rows("19:20").Delete Shift:=xlUp
.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

.Columns("C:C").Delete
.Columns("C:D").Delete
.Rows("1:3").Delete
.Rows("1:1").Insert CopyOrigin:=xlFormatFromLeftOrAbove
.Columns("B:G").Insert CopyOrigin:=xlFormatFromLeftOrAbove
.Columns("B:G").ColumnWidth = 10
.Range("A2:A16").Replace What:=",", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

.Range("A2:A16").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("A:C").Delete
End With

With Sheets("Sheet2")
.Range("A1") = "JAN"
.Range("A2") = "FEB"
.Range("A3") = "MAR"
.Range("A1:A3").AutoFill _
Destination:=.Range("A1:A12"), _
Type:=xlFillDefault

.Range("B1") = "1"
.Range("B2") = "2"
.Range("B3") = "3"
.Range("B1:B3").AutoFill _
Destination:=.Range("B1:B12"), _
Type:=xlFillDefault
End With

With Sheets("Sheet1")
.Columns("A:C").ColumnWidth = 8
Range("D2").FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet2!C[-3]:C[-2],2,FALSE)"
.Range("D2").Copy _
Destination:=.Range("D2:D16")

.Columns("D:D").Copy
.Columns("D:D").PasteSpecial _
Paste:=xlPasteValues

.Columns("A:A").Delete
.Columns("D:D").Insert
.Range("D2").FormulaR1C1 = "=DATE(RC[-2],RC[-1],RC[-3])"
.Range("D2").Copy _
Destination:=.Range("D2:D16")
.Columns("D:D").Copy
.Columns("D:D").PasteSpecial _
Paste:=xlPasteValues

.Columns("A:C").Delete
.Columns("A:M").ColumnWidth = 12.86
.Range("A1") = "DATE"
.Range("B1") = "TIME"
.Range("C1") = "INBOUND"
.Range("D1") = "AVG INBOUND TIME"
.Range("E1") = "ABAND"
.Range("F1") = "AVG ABAND TIME"
.Range("G1") = "AVG TALK TIME"

.Columns("H:J").Delete
.Range("H1") = "TOTAL INTERNAL"
.Range("I1") = "AVG STAFF"
Range("J1") = "% IN SERV LEVEL"

With Cells
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.EntireColumn.AutoFit
End With
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs _
Filename:="Y:\bcms_skill1.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
Application.DisplayAlerts = True
End With
End Sub

Jon Dibble said:
Hi This Macro

Manipluates the data from a csv file then saves itself in a location ready
to be imported into SQL. The finished table looks like this - note that the
DATE column has returned #N/A. When this was recorded it returned a DATE.
Can anyone help or suggest if I could go about this task in an easier
fashion?

DATE TIME INBOUND AVG INBOUND TIME ABAND AVG ABAND TIME AVG TALK TIME
TOTAL INTERNAL AVG STAFF % IN SERV LEVEL
#N/A 08:00 11 00:05 0 00:00 01:07 258:52:00 7 100
#N/A 09:00 76 00:07 0 00:00 01:13 367:35:00 11 82
#N/A 10:00 61 00:07 2 00:03 01:18 383:06:00 11.8 76
#N/A 11:00 91 00:07 1 00:05 01:35 337:08:00 11 85
#N/A 12:00 70 00:06 1 00:09 01:56 337:54:00 11.1 87
#N/A 13:00 44 00:10 1 00:23 01:40 539:20:00 12 67
#N/A 14:00 85 00:08 1 00:10 01:42 413:56:00 12 71
#N/A 15:00 66 00:07 3 00:06 01:46 360:15:00 11 80
#N/A 16:00 61 00:07 0 00:00 01:50 397:56:00 10.7 84
#N/A 17:00 8 00:07 0 00:00 02:15 144:50:00 3.7 75
#N/A 18:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 19:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 20:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 21:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 22:00 0 00:00 0 00:00 00:00 00:00 0




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
CSVFile.Close
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").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("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("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"
Click to expand...
Click to expand...
 
J

Jon Dibble

Hi Joel

I recorded the Macro again and added in my additonal code to ensure it
overwrites the saved file and calls macro on opening and has fixed the date
issue. Have attached the working code below. I am in the UK. Many thanks for
taking the time to look at this for me.

Sub skill1()
'
' skill1 Macro
'

'
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
CSVFile.Close
Cells.Select
Cells.EntireColumn.AutoFit
Columns("A:B").Select
Range("B1").Activate
Selection.Delete Shift:=xlToLeft
Columns("B:G").Select
Range("G1").Activate
Selection.Delete Shift:=xlToLeft
Rows("1:3").Select
Range("A3").Activate
Selection.Delete Shift:=xlUp
Columns("C:D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("16:17").Select
Selection.Delete Shift:=xlUp
Range("B1:B15").Select
Selection.TextToColumns Destination:=Range("B1"), 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:D").Select
Selection.Delete Shift:=xlToLeft
Columns("A:M").Select
Range("M1").Activate
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("B:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 10
Columns("D:E").Select
Range("E1").Activate
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1:A15").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, FieldInfo
_
:=Array(1, 1), TrailingMinusNumbers:=True
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(8, 1), Array(13,
1), Array(17, 1), _
Array(20, 1)), TrailingMinusNumbers:=True
Columns("A:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft
Range("B1:B15").Select
Selection.Replace What:=",", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("D1").Select
ActiveCell.FormulaR1C1 = "=DATE(RC[-1],RC[-3],RC[-2])"
Range("D1").Select
Selection.ClearContents
Sheets("Sheet2").Select
ActiveCell.FormulaR1C1 = "JAN"
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")
Range("B1:B12").Select
Sheets("Sheet1").Select
ActiveCell.FormulaR1C1 =
"=VLOOKUP(RC[-3],Sheet2!RC[-3]:R[11]C[-2],2,FALSE)"
Columns("D:D").Select
Selection.NumberFormat = "0"
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D15")
Range("D1:D15").Select
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D15"), Type:=xlFillDefault
Range("D1:D15").Select
Range("D1:D15").Select
Selection.ClearContents
Range("D1").Select
Sheets("Sheet2").Select
Range("A1").Select
Sheets("Sheet1").Select
ActiveCell.FormulaR1C1 =
"=VLOOKUP(RC[-3],Sheet2!RC[-3]:R[11]C[-2],2,FALSE)"
Selection.AutoFill Destination:=Range("D1:D15"), Type:=xlFillDefault
Range("D1:D15").Select
Range("D1").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet2!R1C1:R12C2,2,FALSE)"
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D16"), Type:=xlFillDefault
Range("D1:D16").Select
Range("D16").Select
Selection.ClearContents
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("D1").Select
ActiveCell.FormulaR1C1 = "=DATE(RC[-2],RC[-1],RC[-3])"
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D15"), Type:=xlFillDefault
Range("D1:D15").Select
Columns("D:D").Select
Selection.Copy
Application.CutCopyMode = False
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:A").Select
Selection.NumberFormat = "dd/mm/yyyy;@"
Range("A1").Select
Columns("A:A").ColumnWidth = 11.71
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "DATE"
Range("B1").Select
ActiveCell.FormulaR1C1 = "TIME"
Range("C1").Select
ActiveCell.FormulaR1C1 = "INBOUND"
Range("D1").Select
Columns("C:C").ColumnWidth = 10.14
Columns("D:D").Select
Selection.ColumnWidth = 11.29
Columns("E:M").Select
Selection.ColumnWidth = 20.57
Range("D1").Select
Columns("D:D").ColumnWidth = 13.43
Range("D1").Select
ActiveCell.FormulaR1C1 = "AVG INBOUND TIME"
Range("E1").Select
ActiveCell.FormulaR1C1 = "AVG TALK TIME"
Range("E1").Select
Selection.ClearContents
Range("D1").Select
Selection.ClearContents
Range("D1").Select
ActiveCell.FormulaR1C1 = "AVG SPEED ANS"
Range("E1").Select
ActiveCell.FormulaR1C1 = "ABAND"
Range("F1").Select
ActiveCell.FormulaR1C1 = "AVG ABAND TIME"
Range("G1").Select
ActiveCell.FormulaR1C1 = "AVG TALK TIME"
Range("H1").Select
ActiveCell.FormulaR1C1 = "TOTAL AFTER CALL"
Range("I1").Select
ActiveCell.FormulaR1C1 = "FLOW IN"
Range("J1").Select
ActiveCell.FormulaR1C1 = "FLOW OUT"
Range("K1").Select
ActiveCell.FormulaR1C1 = "AUX / INTERNAL"
Range("L1").Select
ActiveCell.FormulaR1C1 = "AVG STAFF"
Range("M1").Select
ActiveCell.FormulaR1C1 = "IN SERV LEVEL %"
Cells.Select
With Selection.Font
.Name = "Calibri"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
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



Joel said:
To correct the data I need to see a sample of the original CSV file. OPen
the CSV file with NOTEPAD and copy a few of the rows onto the posting. I
also need to know what coutry youo are processing the data from. Dates in
excel expect different formats depending on the courty. If you are in
England and the Data comes from the US the Month and Day are reversed
which
can cause errors like the one you are seeing.

Joel said:
Most of your code is formating which cannot be eliminate or made easier.
You
don't need to use the select method liike in your original code. The
Selection method takes longer to run and makes the code harder to
understand
and longer to write. I made changes the way I would write the code.
Compare
the differences.


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
CSVFile.Close
With Sheets("Sheet1")
.Cells.EntireColumn.AutoFit
.Columns("A:B").Delete
.Columns("B:G").Delete
.Columns("C:E").Select.Insert CopyOrigin:=xlFormatFromLeftOrAbove
.Rows("19:20").Delete Shift:=xlUp
.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

.Columns("C:C").Delete
.Columns("C:D").Delete
.Rows("1:3").Delete
.Rows("1:1").Insert CopyOrigin:=xlFormatFromLeftOrAbove
.Columns("B:G").Insert CopyOrigin:=xlFormatFromLeftOrAbove
.Columns("B:G").ColumnWidth = 10
.Range("A2:A16").Replace What:=",", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

.Range("A2:A16").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("A:C").Delete
End With

With Sheets("Sheet2")
.Range("A1") = "JAN"
.Range("A2") = "FEB"
.Range("A3") = "MAR"
.Range("A1:A3").AutoFill _
Destination:=.Range("A1:A12"), _
Type:=xlFillDefault

.Range("B1") = "1"
.Range("B2") = "2"
.Range("B3") = "3"
.Range("B1:B3").AutoFill _
Destination:=.Range("B1:B12"), _
Type:=xlFillDefault
End With

With Sheets("Sheet1")
.Columns("A:C").ColumnWidth = 8
Range("D2").FormulaR1C1 =
"=VLOOKUP(RC[-3],Sheet2!C[-3]:C[-2],2,FALSE)"
.Range("D2").Copy _
Destination:=.Range("D2:D16")

.Columns("D:D").Copy
.Columns("D:D").PasteSpecial _
Paste:=xlPasteValues

.Columns("A:A").Delete
.Columns("D:D").Insert
.Range("D2").FormulaR1C1 = "=DATE(RC[-2],RC[-1],RC[-3])"
.Range("D2").Copy _
Destination:=.Range("D2:D16")
.Columns("D:D").Copy
.Columns("D:D").PasteSpecial _
Paste:=xlPasteValues

.Columns("A:C").Delete
.Columns("A:M").ColumnWidth = 12.86
.Range("A1") = "DATE"
.Range("B1") = "TIME"
.Range("C1") = "INBOUND"
.Range("D1") = "AVG INBOUND TIME"
.Range("E1") = "ABAND"
.Range("F1") = "AVG ABAND TIME"
.Range("G1") = "AVG TALK TIME"

.Columns("H:J").Delete
.Range("H1") = "TOTAL INTERNAL"
.Range("I1") = "AVG STAFF"
Range("J1") = "% IN SERV LEVEL"

With Cells
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.EntireColumn.AutoFit
End With
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs _
Filename:="Y:\bcms_skill1.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
Application.DisplayAlerts = True
End With
End Sub

Jon Dibble said:
Hi This Macro

Manipluates the data from a csv file then saves itself in a location
ready
to be imported into SQL. The finished table looks like this - note that
the
DATE column has returned #N/A. When this was recorded it returned a
DATE.
Can anyone help or suggest if I could go about this task in an easier
fashion?

DATE TIME INBOUND AVG INBOUND TIME ABAND AVG ABAND TIME AVG TALK
TIME
TOTAL INTERNAL AVG STAFF % IN SERV LEVEL
#N/A 08:00 11 00:05 0 00:00 01:07 258:52:00 7 100
#N/A 09:00 76 00:07 0 00:00 01:13 367:35:00 11 82
#N/A 10:00 61 00:07 2 00:03 01:18 383:06:00 11.8 76
#N/A 11:00 91 00:07 1 00:05 01:35 337:08:00 11 85
#N/A 12:00 70 00:06 1 00:09 01:56 337:54:00 11.1 87
#N/A 13:00 44 00:10 1 00:23 01:40 539:20:00 12 67
#N/A 14:00 85 00:08 1 00:10 01:42 413:56:00 12 71
#N/A 15:00 66 00:07 3 00:06 01:46 360:15:00 11 80
#N/A 16:00 61 00:07 0 00:00 01:50 397:56:00 10.7 84
#N/A 17:00 8 00:07 0 00:00 02:15 144:50:00 3.7 75
#N/A 18:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 19:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 20:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 21:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 22:00 0 00:00 0 00:00 00:00 00:00 0




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
CSVFile.Close
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").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("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("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"
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

Complie Syntax Error 6
VBA Validation between two Times 17
criteriarange not equal to 4
"=TEXT(RC[-1],"MMM")" 4
Different Results from the Same Macro 3
Unwanted Querytable Autofilter on ODBC Import 4
Insert columns and values programmatically with IF statement 9
please help.is it possible re-edit macro to convert text like1530-2200 to time format automatically? 1

Top