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...
 
J

Joel

Sub GetIDS()

Set OctSht = Sheets("October")
Set NovSht = Sheets("November")
Set IDSht = Sheets("ID")

IDSht.Cells.ClearContents

'Copy October IDs to ID Sheet
OctSht.Columns("B").Copy _
Destination:=IDSht.Columns("A")
'Delete Header Row from ID Sheet
IDSht.Rows(1).Delete

LastRow = IDSht.Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1

'Copy November IDs to ID Sheet, skip Header
LastRow = NovSht.Range("A" & Rows.Count).End(xlUp).Row
NovSht.Range("B2:B" & LastRow).Copy _
Destination:=IDSht.Range("A" & NewRow)

'sort ID's
With IDSht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Rows("1:" & LastRow).Sort _
key1:=.Range("A1"), _
order1:=xlAscending, _
header:=xlNo

'Remove duplicate IDs
RowCount = 1
Do While .Range("A" & RowCount) <> ""
If .Range("A" & RowCount) = .Range("A" & (RowCount + 1)) Then
.Rows(RowCount + 1).Delete
Else
RowCount = RowCount + 1
End If
Loop
End With
End Sub
Sub MatchQuery()

Set OctSht = Sheets("October")
Set NovSht = Sheets("November")
Set OctOnly = Sheets("Oct Only")
Set NovOnly = Sheets("Nov Only")
Set MatchBoth = Sheets("Match")
Set IDSht = Sheets("ID")

LastRow = IDSht.Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 1 To LastRow
ID = IDSht.Range("A" & RowCount)

Set FoundOct = OctSht.Columns("B").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)

Set FoundNov = NovSht.Columns("B").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)

If FoundOct Is Nothing Then

If FoundNov Is Nothing Then
MsgBox ("ID : " & ID & " is not found")
Else
'found in November only
With NovOnly
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
NovSht.Range("A" & FoundNov.Row & ":I" & FoundNov.Row).Copy _
Destination:=.Range("A" & NewRow)
End With
End If
Else
If FoundNov Is Nothing Then
'Found in October Only
With OctOnly
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
OctSht.Range("A" & FoundOct.Row & ":I" & FoundOct.Row).Copy _
Destination:=.Range("A" & NewRow)
End With
Else
'Found in both October and November
With MatchBoth
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
OctSht.Range("A" & FoundOct.Row & ":I" & FoundOct.Row).Copy _
Destination:=.Range("A" & NewRow)
NovSht.Range("A" & FoundNov.Row & ":I" & FoundNov.Row).Copy _
Destination:=.Range("J" & NewRow)
End With
End If
End If
Next RowCount
End Sub




Jon Dibble said:
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.
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
Macro should Save and Overwrite file 1
Different Results from the Same Macro 2
"=TEXT(RC[-1],"MMM")" 4
criteriarange not equal to 4
excel macro missing worksheets 9
Run Macro through an entire range 2
variable number of rows. 2

Top