ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Macro not returning correct data (https://www.excelbanter.com/excel-programming/422217-macro-not-returning-correct-data.html)

Jon Dibble
Macro not returning correct data
 
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_skil l_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

joel
Macro not returning correct data
 
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" wrote:

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_skil l_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

joel
Macro not returning correct data
 
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" wrote:

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" wrote:

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_skil l_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"

Jon Dibble
Macro not returning correct data
 
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_skil l_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" wrote in message
...
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" wrote:

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" wrote:

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_skil l_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"


joel
Macro not returning correct data
 
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" wrote:

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_skil l_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" wrote in message
...
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" wrote:

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, _


All times are GMT +1. The time now is 06:10 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com