PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Programming > Complie Syntax Error

Reply
Thread Tools Rate Thread

Complie Syntax Error

 
 
Jon Dibble
Guest
Posts: n/a
 
      8th Jan 2009
Hi
Am very new to VB and am having trouble with this Macro. I f anyone could
help it would be really appreciated.

Sub bcms()
'
'
'

Set NewSht = ThisWorkbook.ActiveSheet
ChDir "Y:\"
Set CSVFile =
Workbooks.Open(Filename:="Y:\report_list_bcms_skill_1_.csv")
CSVFile.ActiveSheet.Range("A1:U20").Copy _
Destination:=NewSht.Range("A1")
Application.CutCopyMode = False
Cells.Select
Cells.EntireColumn.AutoFit
Columns("A:B").Select
Range("B1").Activate
Selection.Delete Shift:=xlToLeft
Columns("B:G").Select
Selection.Delete Shift:=xlToLeft
Columns("C:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("19:20").Select
Selection.Delete Shift:=xlUp
Range("B4:B18").Select Selection.TextToColumns Destination:=Range("B4"),
DataType:=xlDelimited,TextQualifier:=xlDoubleQuote,ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar
_
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
TrailingMinusNumbers:=True
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Columns("C").Select
Selection.Delete Shift:=xlToLeft
Rows("1:3").Select
Range("A3").Activate
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("B:G").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 10
Range("A2:A16").Select
Selection.Replace What:=",", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.TextToColumns Destination:=Range("A2"),
DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(8,
1), Array(13, _
1), Array(17, 1), Array(19, 1)), TrailingMinusNumbers:=True
Columns("A:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft
Range("A2").Select
Sheets("Sheet2").Select
ActiveCell.FormulaR1C1 = "JAN"
Range("A3").Select
ActiveCell.FormulaR1C1 = "FEB"
Range("A3").Select
Selection.ClearContents
Range("A2").Select
ActiveCell.FormulaR1C1 = "FEB"
Range("A3").Select
ActiveCell.FormulaR1C1 = "MAR"
Range("A1:A3").Select
Selection.AutoFill Destination:=Range("A1:A12"), Type:=xlFillDefault
Range("A1:A12").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "1"
Range("B2").Select
ActiveCell.FormulaR1C1 = "2"
Range("B3").Select
ActiveCell.FormulaR1C1 = "3"
Range("B1:B3").Select
Selection.AutoFill Destination:=Range("B1:B12"), Type:=xlFillDefault
Range("B1:B12").Select
Sheets("Sheet1").Select
Columns("A:C").Select
Range("C1").Activate
Selection.ColumnWidth = 8
Range("D2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet2!C[-3]:C[-2],2,FALSE)"
Selection.AutoFill Destination:=Range("D216"), Type:=xlFillDefault
Range("D216").Select
Columns("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").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("D216")
Range("D216").Select
Columns("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
 
Reply With Quote
 
 
 
 
Shasur
Guest
Posts: n/a
 
      8th Jan 2009
Hi

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

Sub bcms1()
'
'
'

On Error GoTo Err_Trap

Set NewSht = ThisWorkbook.ActiveSheet


ChDir "y:\"
Set CSVFile = Workbooks.Open(Filename:="y:\report_list_bcms_skill_1_.csv")
CSVFile.ActiveSheet.Range("A1:U20").Copy _
Destination:=NewSht.Range("A1")
Application.CutCopyMode = False
Cells.Select
Cells.EntireColumn.AutoFit
Columns("A:B").Select
Range("B1").Activate
Selection.Delete Shift:=xlToLeft
Columns("B:G").Select
Selection.Delete Shift:=xlToLeft
Columns("C:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("19:20").Select
Selection.Delete Shift:=xlUp
Range("B4:B18").Select
Selection.TextToColumns Destination:=Range("B4"), DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
TrailingMinusNumbers:=True
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Columns("C").Select
Selection.Delete Shift:=xlToLeft
Rows("1:3").Select
Range("A3").Activate
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("B:G").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 10
Range("A2:A16").Select
Selection.Replace What:=",", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.TextToColumns Destination:=Range("A2"),
DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(8,
1), Array(13, _
1), Array(17, 1), Array(19, 1)), TrailingMinusNumbers:=True
Columns("A:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft
Range("A2").Select
Sheets("Sheet2").Select
ActiveCell.FormulaR1C1 = "JAN"
Range("A3").Select
ActiveCell.FormulaR1C1 = "FEB"
Range("A3").Select
Selection.ClearContents
Range("A2").Select
ActiveCell.FormulaR1C1 = "FEB"
Range("A3").Select
ActiveCell.FormulaR1C1 = "MAR"
Range("A1:A3").Select
Selection.AutoFill Destination:=Range("A1:A12"), Type:=xlFillDefault
Range("A1:A12").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "1"
Range("B2").Select
ActiveCell.FormulaR1C1 = "2"
Range("B3").Select
ActiveCell.FormulaR1C1 = "3"
Range("B1:B3").Select
Selection.AutoFill Destination:=Range("B1:B12"), Type:=xlFillDefault
Range("B1:B12").Select
Sheets("Sheet1").Select
Columns("A:C").Select
Range("C1").Activate
Selection.ColumnWidth = 8
Range("D2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet2!C[-3]:C[-2],2,FALSE)"
Selection.AutoFill Destination:=Range("D216"), Type:=xlFillDefault
Range("D216").Select
Columns("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").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("D216")
Range("D216").Select
Columns("D").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("A:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft
Columns("A:M").Select
Selection.ColumnWidth = 12.86
Range("A1").Select
ActiveCell.FormulaR1C1 = "DATE"
Range("B1").Select
ActiveCell.FormulaR1C1 = "TIME"
Range("C1").Select
ActiveCell.FormulaR1C1 = "INBOUND"
Range("D1").Select
ActiveCell.FormulaR1C1 = "AVG INBOUND TIME"
Range("E1").Select
ActiveCell.FormulaR1C1 = "ABAND"
Range("F1").Select
ActiveCell.FormulaR1C1 = "AVG ABAND TIME"
Range("G1").Select
ActiveCell.FormulaR1C1 = "AVG TALK TIME"
Columns("H:J").Select
Selection.Delete Shift:=xlToLeft
Range("H1").Select
ActiveCell.FormulaR1C1 = "TOTAL INTERNAL"
Range("I1").Select
ActiveCell.FormulaR1C1 = "AVG STAFF"
Range("J1").Select
ActiveCell.FormulaR1C1 = "% IN SERV LEVEL"
Cells.Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Cells.EntireColumn.AutoFit
Range("A2").Select
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="y:\bcms_skill1.xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Application.DisplayAlerts = True

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


Cheers
Shasur

--
http://vbadud.blogspot.com


"Jon Dibble" wrote:

> Hi
> Am very new to VB and am having trouble with this Macro. I f anyone could
> help it would be really appreciated.
>
> Sub bcms()
> '
> '
> '
>
> Set NewSht = ThisWorkbook.ActiveSheet
> ChDir "Y:\"
> Set CSVFile =
> Workbooks.Open(Filename:="Y:\report_list_bcms_skill_1_.csv")
> CSVFile.ActiveSheet.Range("A1:U20").Copy _
> Destination:=NewSht.Range("A1")
> Application.CutCopyMode = False
> Cells.Select
> Cells.EntireColumn.AutoFit
> Columns("A:B").Select
> Range("B1").Activate
> Selection.Delete Shift:=xlToLeft
> Columns("B:G").Select
> Selection.Delete Shift:=xlToLeft
> Columns("C:E").Select
> Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
> Rows("19:20").Select
> Selection.Delete Shift:=xlUp
> Range("B4:B18").Select Selection.TextToColumns Destination:=Range("B4"),
> DataType:=xlDelimited,TextQualifier:=xlDoubleQuote,ConsecutiveDelimiter:=False,
> Tab:=True, _
> Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar
> _
> :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
> TrailingMinusNumbers:=True
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
> Columns("C").Select
> Selection.Delete Shift:=xlToLeft
> Rows("1:3").Select
> Range("A3").Activate
> Selection.Delete Shift:=xlUp
> Rows("1:1").Select
> Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
> Columns("B:G").Select
> Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
> Selection.ColumnWidth = 10
> Range("A2:A16").Select
> Selection.Replace What:=",", Replacement:="", LookAt:=xlPart, _
> SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
> ReplaceFormat:=False
> Selection.TextToColumns Destination:=Range("A2"),
> DataType:=xlFixedWidth, _
> OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(8,
> 1), Array(13, _
> 1), Array(17, 1), Array(19, 1)), TrailingMinusNumbers:=True
> Columns("A:C").Select
> Range("C1").Activate
> Selection.Delete Shift:=xlToLeft
> Range("A2").Select
> Sheets("Sheet2").Select
> ActiveCell.FormulaR1C1 = "JAN"
> Range("A3").Select
> ActiveCell.FormulaR1C1 = "FEB"
> Range("A3").Select
> Selection.ClearContents
> Range("A2").Select
> ActiveCell.FormulaR1C1 = "FEB"
> Range("A3").Select
> ActiveCell.FormulaR1C1 = "MAR"
> Range("A1:A3").Select
> Selection.AutoFill Destination:=Range("A1:A12"), Type:=xlFillDefault
> Range("A1:A12").Select
> Range("B1").Select
> ActiveCell.FormulaR1C1 = "1"
> Range("B2").Select
> ActiveCell.FormulaR1C1 = "2"
> Range("B3").Select
> ActiveCell.FormulaR1C1 = "3"
> Range("B1:B3").Select
> Selection.AutoFill Destination:=Range("B1:B12"), Type:=xlFillDefault
> Range("B1:B12").Select
> Sheets("Sheet1").Select
> Columns("A:C").Select
> Range("C1").Activate
> Selection.ColumnWidth = 8
> Range("D2").Select
> ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet2!C[-3]:C[-2],2,FALSE)"
> Selection.AutoFill Destination:=Range("D216"), Type:=xlFillDefault
> Range("D216").Select
> Columns("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").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("D216")
> Range("D216").Select
> Columns("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
>
>
>
>
>
>
>
 
Reply With Quote
 
Jon Dibble
Guest
Posts: n/a
 
      8th Jan 2009
It highlights the following code

Selection.TextToColumns Destination:=Range("B4"),
DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True,
OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
TrailingMinusNumbers:=True

Then

Selection.TextToColumns Destination:=Range("A2"),
DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(8,
1), Array(13, _
1), Array(17, 1), Array(19, 1)), TrailingMinusNumbers:=True

Then

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

And finally!!!

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


"Shasur" <(E-Mail Removed)> wrote in message
news:9307939B-3B5B-4E27-BD86-(E-Mail Removed)...
> Hi
>
> I have added error handling code to ur code and formatted a bit. Try the
> following and check the line where error is thrown
>
> Sub bcms1()
> '
> '
> '
>
> On Error GoTo Err_Trap
>
> Set NewSht = ThisWorkbook.ActiveSheet
>
>
> ChDir "y:\"
> Set CSVFile =
> Workbooks.Open(Filename:="y:\report_list_bcms_skill_1_.csv")
> CSVFile.ActiveSheet.Range("A1:U20").Copy _
> Destination:=NewSht.Range("A1")
> Application.CutCopyMode = False
> Cells.Select
> Cells.EntireColumn.AutoFit
> Columns("A:B").Select
> Range("B1").Activate
> Selection.Delete Shift:=xlToLeft
> Columns("B:G").Select
> Selection.Delete Shift:=xlToLeft
> Columns("C:E").Select
> Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
> Rows("19:20").Select
> Selection.Delete Shift:=xlUp
> Range("B4:B18").Select
> Selection.TextToColumns Destination:=Range("B4"),
> DataType:=xlDelimited,
> TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
> Semicolon:=False, Comma:=False, Space:=False, Other:=True,
> OtherChar _
> :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
> TrailingMinusNumbers:=True
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
> Columns("C").Select
> Selection.Delete Shift:=xlToLeft
> Rows("1:3").Select
> Range("A3").Activate
> Selection.Delete Shift:=xlUp
> Rows("1:1").Select
> Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
> Columns("B:G").Select
> Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
> Selection.ColumnWidth = 10
> Range("A2:A16").Select
> Selection.Replace What:=",", Replacement:="", LookAt:=xlPart, _
> SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
> ReplaceFormat:=False
> Selection.TextToColumns Destination:=Range("A2"),
> DataType:=xlFixedWidth, _
> OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(8,
> 1), Array(13, _
> 1), Array(17, 1), Array(19, 1)), TrailingMinusNumbers:=True
> Columns("A:C").Select
> Range("C1").Activate
> Selection.Delete Shift:=xlToLeft
> Range("A2").Select
> Sheets("Sheet2").Select
> ActiveCell.FormulaR1C1 = "JAN"
> Range("A3").Select
> ActiveCell.FormulaR1C1 = "FEB"
> Range("A3").Select
> Selection.ClearContents
> Range("A2").Select
> ActiveCell.FormulaR1C1 = "FEB"
> Range("A3").Select
> ActiveCell.FormulaR1C1 = "MAR"
> Range("A1:A3").Select
> Selection.AutoFill Destination:=Range("A1:A12"), Type:=xlFillDefault
> Range("A1:A12").Select
> Range("B1").Select
> ActiveCell.FormulaR1C1 = "1"
> Range("B2").Select
> ActiveCell.FormulaR1C1 = "2"
> Range("B3").Select
> ActiveCell.FormulaR1C1 = "3"
> Range("B1:B3").Select
> Selection.AutoFill Destination:=Range("B1:B12"), Type:=xlFillDefault
> Range("B1:B12").Select
> Sheets("Sheet1").Select
> Columns("A:C").Select
> Range("C1").Activate
> Selection.ColumnWidth = 8
> Range("D2").Select
> ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet2!C[-3]:C[-2],2,FALSE)"
> Selection.AutoFill Destination:=Range("D216"), Type:=xlFillDefault
> Range("D216").Select
> Columns("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").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("D216")
> Range("D216").Select
> Columns("D").Select
> Selection.Copy
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Application.CutCopyMode = False
> Columns("A:C").Select
> Range("C1").Activate
> Selection.Delete Shift:=xlToLeft
> Columns("A:M").Select
> Selection.ColumnWidth = 12.86
> Range("A1").Select
> ActiveCell.FormulaR1C1 = "DATE"
> Range("B1").Select
> ActiveCell.FormulaR1C1 = "TIME"
> Range("C1").Select
> ActiveCell.FormulaR1C1 = "INBOUND"
> Range("D1").Select
> ActiveCell.FormulaR1C1 = "AVG INBOUND TIME"
> Range("E1").Select
> ActiveCell.FormulaR1C1 = "ABAND"
> Range("F1").Select
> ActiveCell.FormulaR1C1 = "AVG ABAND TIME"
> Range("G1").Select
> ActiveCell.FormulaR1C1 = "AVG TALK TIME"
> Columns("H:J").Select
> Selection.Delete Shift:=xlToLeft
> Range("H1").Select
> ActiveCell.FormulaR1C1 = "TOTAL INTERNAL"
> Range("I1").Select
> ActiveCell.FormulaR1C1 = "AVG STAFF"
> Range("J1").Select
> ActiveCell.FormulaR1C1 = "% IN SERV LEVEL"
> Cells.Select
> With Selection
> .HorizontalAlignment = xlLeft
> .VerticalAlignment = xlBottom
> .WrapText = False
> .Orientation = 0
> .AddIndent = False
> .IndentLevel = 0
> .ShrinkToFit = False
> .ReadingOrder = xlContext
> .MergeCells = False
> End With
> Cells.EntireColumn.AutoFit
> Range("A2").Select
> Application.DisplayAlerts = False
> ActiveWorkbook.SaveAs Filename:="y:\bcms_skill1.xlsm", FileFormat:= _
> xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
> Application.DisplayAlerts = True
>
> Exit Sub
> Err_Trap:
> Debug.Assert Err <> 0
> Err.Clear
> Resume Next
> End Sub
>
>
> Cheers
> Shasur
>
> --
> http://vbadud.blogspot.com
>
>
> "Jon Dibble" wrote:
>
>> Hi
>> Am very new to VB and am having trouble with this Macro. I f anyone could
>> help it would be really appreciated.
>>
>> Sub bcms()
>> '
>> '
>> '
>>
>> Set NewSht = ThisWorkbook.ActiveSheet
>> ChDir "Y:\"
>> Set CSVFile =
>> Workbooks.Open(Filename:="Y:\report_list_bcms_skill_1_.csv")
>> CSVFile.ActiveSheet.Range("A1:U20").Copy _
>> Destination:=NewSht.Range("A1")
>> Application.CutCopyMode = False
>> Cells.Select
>> Cells.EntireColumn.AutoFit
>> Columns("A:B").Select
>> Range("B1").Activate
>> Selection.Delete Shift:=xlToLeft
>> Columns("B:G").Select
>> Selection.Delete Shift:=xlToLeft
>> Columns("C:E").Select
>> Selection.Insert Shift:=xlToRight,
>> CopyOrigin:=xlFormatFromLeftOrAbove
>> Rows("19:20").Select
>> Selection.Delete Shift:=xlUp
>> Range("B4:B18").Select Selection.TextToColumns
>> Destination:=Range("B4"),
>> DataType:=xlDelimited,TextQualifier:=xlDoubleQuote,ConsecutiveDelimiter:=False,
>> Tab:=True, _
>> Semicolon:=False, Comma:=False, Space:=False, Other:=True,
>> OtherChar
>> _
>> :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
>> TrailingMinusNumbers:=True
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>> Columns("C").Select
>> Selection.Delete Shift:=xlToLeft
>> Rows("1:3").Select
>> Range("A3").Activate
>> Selection.Delete Shift:=xlUp
>> Rows("1:1").Select
>> Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
>> Columns("B:G").Select
>> Selection.Insert Shift:=xlToRight,
>> CopyOrigin:=xlFormatFromLeftOrAbove
>> Selection.ColumnWidth = 10
>> Range("A2:A16").Select
>> Selection.Replace What:=",", Replacement:="", LookAt:=xlPart, _
>> SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
>> ReplaceFormat:=False
>> Selection.TextToColumns Destination:=Range("A2"),
>> DataType:=xlFixedWidth, _
>> OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(5, 1),
>> Array(8,
>> 1), Array(13, _
>> 1), Array(17, 1), Array(19, 1)), TrailingMinusNumbers:=True
>> Columns("A:C").Select
>> Range("C1").Activate
>> Selection.Delete Shift:=xlToLeft
>> Range("A2").Select
>> Sheets("Sheet2").Select
>> ActiveCell.FormulaR1C1 = "JAN"
>> Range("A3").Select
>> ActiveCell.FormulaR1C1 = "FEB"
>> Range("A3").Select
>> Selection.ClearContents
>> Range("A2").Select
>> ActiveCell.FormulaR1C1 = "FEB"
>> Range("A3").Select
>> ActiveCell.FormulaR1C1 = "MAR"
>> Range("A1:A3").Select
>> Selection.AutoFill Destination:=Range("A1:A12"), Type:=xlFillDefault
>> Range("A1:A12").Select
>> Range("B1").Select
>> ActiveCell.FormulaR1C1 = "1"
>> Range("B2").Select
>> ActiveCell.FormulaR1C1 = "2"
>> Range("B3").Select
>> ActiveCell.FormulaR1C1 = "3"
>> Range("B1:B3").Select
>> Selection.AutoFill Destination:=Range("B1:B12"), Type:=xlFillDefault
>> Range("B1:B12").Select
>> Sheets("Sheet1").Select
>> Columns("A:C").Select
>> Range("C1").Activate
>> Selection.ColumnWidth = 8
>> Range("D2").Select
>> ActiveCell.FormulaR1C1 =
>> "=VLOOKUP(RC[-3],Sheet2!C[-3]:C[-2],2,FALSE)"
>> Selection.AutoFill Destination:=Range("D216"), Type:=xlFillDefault
>> Range("D216").Select
>> Columns("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").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("D216")
>> Range("D216").Select
>> Columns("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
>>
>>
>>
>>
>>
>>
>>
 
Reply With Quote
 
Bob Phillips
Guest
Posts: n/a
 
      8th Jan 2009
Ithin k it is this line


Range("B4:B18").Select Selection.TextToColumns Destination:=Range("B4"),
DataType:=xlDelimited,TextQualifier:=xlDoubleQuote,ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar
_
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
TrailingMinusNumbers:=True

change it to

Range("B4:B18").TextToColumns Destination:=Range("B4"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=True, _
OtherChar:="-", _
FieldInfo:=Array(Array(1, 1), Array(2,
1)), _
TrailingMinusNumbers:=True

--
__________________________________
HTH

Bob

"Jon Dibble" <(E-Mail Removed)> wrote in message
news:40631FD6-BF7C-4F7E-9BFA-(E-Mail Removed)...
> Hi
> Am very new to VB and am having trouble with this Macro. I f anyone could
> help it would be really appreciated.
>
> Sub bcms()
> '
> '
> '
>
> Set NewSht = ThisWorkbook.ActiveSheet
> ChDir "Y:\"
> Set CSVFile =
> Workbooks.Open(Filename:="Y:\report_list_bcms_skill_1_.csv")
> CSVFile.ActiveSheet.Range("A1:U20").Copy _
> Destination:=NewSht.Range("A1")
> Application.CutCopyMode = False
> Cells.Select
> Cells.EntireColumn.AutoFit
> Columns("A:B").Select
> Range("B1").Activate
> Selection.Delete Shift:=xlToLeft
> Columns("B:G").Select
> Selection.Delete Shift:=xlToLeft
> Columns("C:E").Select
> Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
> Rows("19:20").Select
> Selection.Delete Shift:=xlUp
> Range("B4:B18").Select Selection.TextToColumns
> Destination:=Range("B4"),
> DataType:=xlDelimited,TextQualifier:=xlDoubleQuote,ConsecutiveDelimiter:=False,
> Tab:=True, _
> Semicolon:=False, Comma:=False, Space:=False, Other:=True,
> OtherChar
> _
> :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
> TrailingMinusNumbers:=True
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
> Columns("C").Select
> Selection.Delete Shift:=xlToLeft
> Rows("1:3").Select
> Range("A3").Activate
> Selection.Delete Shift:=xlUp
> Rows("1:1").Select
> Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
> Columns("B:G").Select
> Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
> Selection.ColumnWidth = 10
> Range("A2:A16").Select
> Selection.Replace What:=",", Replacement:="", LookAt:=xlPart, _
> SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
> ReplaceFormat:=False
> Selection.TextToColumns Destination:=Range("A2"),
> DataType:=xlFixedWidth, _
> OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(8,
> 1), Array(13, _
> 1), Array(17, 1), Array(19, 1)), TrailingMinusNumbers:=True
> Columns("A:C").Select
> Range("C1").Activate
> Selection.Delete Shift:=xlToLeft
> Range("A2").Select
> Sheets("Sheet2").Select
> ActiveCell.FormulaR1C1 = "JAN"
> Range("A3").Select
> ActiveCell.FormulaR1C1 = "FEB"
> Range("A3").Select
> Selection.ClearContents
> Range("A2").Select
> ActiveCell.FormulaR1C1 = "FEB"
> Range("A3").Select
> ActiveCell.FormulaR1C1 = "MAR"
> Range("A1:A3").Select
> Selection.AutoFill Destination:=Range("A1:A12"), Type:=xlFillDefault
> Range("A1:A12").Select
> Range("B1").Select
> ActiveCell.FormulaR1C1 = "1"
> Range("B2").Select
> ActiveCell.FormulaR1C1 = "2"
> Range("B3").Select
> ActiveCell.FormulaR1C1 = "3"
> Range("B1:B3").Select
> Selection.AutoFill Destination:=Range("B1:B12"), Type:=xlFillDefault
> Range("B1:B12").Select
> Sheets("Sheet1").Select
> Columns("A:C").Select
> Range("C1").Activate
> Selection.ColumnWidth = 8
> Range("D2").Select
> ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet2!C[-3]:C[-2],2,FALSE)"
> Selection.AutoFill Destination:=Range("D216"), Type:=xlFillDefault
> Range("D216").Select
> Columns("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").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("D216")
> Range("D216").Select
> Columns("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
>
>
>
>
>
>
>
 
Reply With Quote
 
Jon Dibble
Guest
Posts: n/a
 
      8th Jan 2009
Thanks Bob thats fixed the first code error

But still having issues with

Selection.TextToColumns.Destination:=Range("A2"),DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(5, 1),
Array(8,1), Array(13, _
1), Array(17, 1), Array(19, 1)), TrailingMinusNumbers:=True

And

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

And

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





"Bob Phillips" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Ithin k it is this line
>
>
> Range("B4:B18").Select Selection.TextToColumns
> Destination:=Range("B4"),
> DataType:=xlDelimited,TextQualifier:=xlDoubleQuote,ConsecutiveDelimiter:=False,
> Tab:=True, _
> Semicolon:=False, Comma:=False, Space:=False, Other:=True,
> OtherChar
> _
> :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
> TrailingMinusNumbers:=True
>
> change it to
>
> Range("B4:B18").TextToColumns Destination:=Range("B4"), _
> DataType:=xlDelimited, _
> TextQualifier:=xlDoubleQuote, _
> ConsecutiveDelimiter:=False, _
> Tab:=True, _
> Semicolon:=False, _
> Comma:=False, _
> Space:=False, _
> Other:=True, _
> OtherChar:="-", _
> FieldInfo:=Array(Array(1, 1), Array(2,
> 1)), _
> TrailingMinusNumbers:=True
>
> --
> __________________________________
> HTH
>
> Bob
>
> "Jon Dibble" <(E-Mail Removed)> wrote in message
> news:40631FD6-BF7C-4F7E-9BFA-(E-Mail Removed)...
>> Hi
>> Am very new to VB and am having trouble with this Macro. I f anyone could
>> help it would be really appreciated.
>>
>> Sub bcms()
>> '
>> '
>> '
>>
>> Set NewSht = ThisWorkbook.ActiveSheet
>> ChDir "Y:\"
>> Set CSVFile =
>> Workbooks.Open(Filename:="Y:\report_list_bcms_skill_1_.csv")
>> CSVFile.ActiveSheet.Range("A1:U20").Copy _
>> Destination:=NewSht.Range("A1")
>> Application.CutCopyMode = False
>> Cells.Select
>> Cells.EntireColumn.AutoFit
>> Columns("A:B").Select
>> Range("B1").Activate
>> Selection.Delete Shift:=xlToLeft
>> Columns("B:G").Select
>> Selection.Delete Shift:=xlToLeft
>> Columns("C:E").Select
>> Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
>> Rows("19:20").Select
>> Selection.Delete Shift:=xlUp
>> Range("B4:B18").Select Selection.TextToColumns
>> Destination:=Range("B4"),
>> DataType:=xlDelimited,TextQualifier:=xlDoubleQuote,ConsecutiveDelimiter:=False,
>> Tab:=True, _
>> Semicolon:=False, Comma:=False, Space:=False, Other:=True,
>> OtherChar
>> _
>> :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
>> TrailingMinusNumbers:=True
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>> Columns("C").Select
>> Selection.Delete Shift:=xlToLeft
>> Rows("1:3").Select
>> Range("A3").Activate
>> Selection.Delete Shift:=xlUp
>> Rows("1:1").Select
>> Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
>> Columns("B:G").Select
>> Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
>> Selection.ColumnWidth = 10
>> Range("A2:A16").Select
>> Selection.Replace What:=",", Replacement:="", LookAt:=xlPart, _
>> SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
>> ReplaceFormat:=False
>> Selection.TextToColumns Destination:=Range("A2"),
>> DataType:=xlFixedWidth, _
>> OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(5, 1),
>> Array(8,
>> 1), Array(13, _
>> 1), Array(17, 1), Array(19, 1)), TrailingMinusNumbers:=True
>> Columns("A:C").Select
>> Range("C1").Activate
>> Selection.Delete Shift:=xlToLeft
>> Range("A2").Select
>> Sheets("Sheet2").Select
>> ActiveCell.FormulaR1C1 = "JAN"
>> Range("A3").Select
>> ActiveCell.FormulaR1C1 = "FEB"
>> Range("A3").Select
>> Selection.ClearContents
>> Range("A2").Select
>> ActiveCell.FormulaR1C1 = "FEB"
>> Range("A3").Select
>> ActiveCell.FormulaR1C1 = "MAR"
>> Range("A1:A3").Select
>> Selection.AutoFill Destination:=Range("A1:A12"), Type:=xlFillDefault
>> Range("A1:A12").Select
>> Range("B1").Select
>> ActiveCell.FormulaR1C1 = "1"
>> Range("B2").Select
>> ActiveCell.FormulaR1C1 = "2"
>> Range("B3").Select
>> ActiveCell.FormulaR1C1 = "3"
>> Range("B1:B3").Select
>> Selection.AutoFill Destination:=Range("B1:B12"), Type:=xlFillDefault
>> Range("B1:B12").Select
>> Sheets("Sheet1").Select
>> Columns("A:C").Select
>> Range("C1").Activate
>> Selection.ColumnWidth = 8
>> Range("D2").Select
>> ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet2!C[-3]:C[-2],2,FALSE)"
>> Selection.AutoFill Destination:=Range("D216"), Type:=xlFillDefault
>> Range("D216").Select
>> Columns("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").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("D216")
>> Range("D216").Select
>> Columns("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
>>
>>
>>
>>
>>
>>
>>
>
>
 
Reply With Quote
 
Per Jessen
Guest
Posts: n/a
 
      8th Jan 2009
Hi
I think the problem is that CSVFile is the active workbook, which you
don't want to manipulate (I guess!)

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

CSVFile.close

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

Selection.TextToColumns Destination:=Range("B4"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False,
Other:=True, _
OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
TrailingMinusNumbers:=True


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

Regards,
Per


On 8 Jan., 15:48, "Jon Dibble" <dibble...@btinternet.com> wrote:
> It highlights the following code
>
> * * * * Selection.TextToColumns Destination:=Range("B4"),
> DataType:=xlDelimited,
> * * TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
> * * * * * * Semicolon:=False, Comma:=False, Space:=False, Other:=True,
> OtherChar _
> * * * * * * :="-", FieldInfo:=Array(Array(1, 1), Array(2,1)),
> * * TrailingMinusNumbers:=True
>
> Then
>
> * * Selection.TextToColumns Destination:=Range("A2"),
> DataType:=xlFixedWidth, _
> * * * * OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(8,
> 1), Array(13, _
> * * * * 1), Array(17, 1), Array(19, 1)), TrailingMinusNumbers:=True
>
> Then
>
> * * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> * * * * :=False, Transpose:=False
>
> And finally!!!
>
> * * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> * * * * :=False, Transpose:=False
>
> "Shasur" <Sha...@discussions.microsoft.com> wrote in message
>
> news:9307939B-3B5B-4E27-BD86-(E-Mail Removed)...
>
>
>
> > Hi
>
> > I have added error handling code to ur code and formatted a bit. Try the
> > following and check the line where error is thrown
>
> > Sub bcms1()
> > '
> > '
> > '
>
> > On Error GoTo Err_Trap
>
> > Set NewSht = ThisWorkbook.ActiveSheet
>
> > * *ChDir "y:\"
> > * *Set CSVFile =
> > Workbooks.Open(Filename:="y:\report_list_bcms_skill_1_.csv")
> > * *CSVFile.ActiveSheet.Range("A1:U20").Copy _
> > * *Destination:=NewSht.Range("A1")
> > * *Application.CutCopyMode = False
> > * *Cells.Select
> > * *Cells.EntireColumn.AutoFit
> > * *Columns("A:B").Select
> > * *Range("B1").Activate
> > * *Selection.Delete Shift:=xlToLeft
> > * *Columns("B:G").Select
> > * *Selection.Delete Shift:=xlToLeft
> > * *Columns("C:E").Select
> > * *Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
> > * *Rows("19:20").Select
> > * *Selection.Delete Shift:=xlUp
> > * *Range("B4:B18").Select
> > * *Selection.TextToColumns Destination:=Range("B4"),
> > DataType:=xlDelimited,
> > TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
> > * * * *Semicolon:=False, Comma:=False, Space:=False, Other:=True,
> > OtherChar _
> > * * * *:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
> > TrailingMinusNumbers:=True
> > * *Columns("C:C").Select
> > * *Selection.Delete Shift:=xlToLeft
> > * *Columns("C").Select
> > * *Selection.Delete Shift:=xlToLeft
> > * *Rows("1:3").Select
> > * *Range("A3").Activate
> > * *Selection.Delete Shift:=xlUp
> > * *Rows("1:1").Select
> > * *Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
> > * *Columns("B:G").Select
> > * *Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
> > * *Selection.ColumnWidth = 10
> > * *Range("A2:A16").Select
> > * *Selection.Replace What:=",", Replacement:="", LookAt:=xlPart, _
> > * * * *SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
> > * * * *ReplaceFormat:=False
> > * *Selection.TextToColumns Destination:=Range("A2"),
> > DataType:=xlFixedWidth, _
> > * * * *OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(8,
> > 1), Array(13, _
> > * * * *1), Array(17, 1), Array(19, 1)), TrailingMinusNumbers:=True
> > * *Columns("A:C").Select
> > * *Range("C1").Activate
> > * *Selection.Delete Shift:=xlToLeft
> > * *Range("A2").Select
> > * *Sheets("Sheet2").Select
> > * *ActiveCell.FormulaR1C1 = "JAN"
> > * *Range("A3").Select
> > * *ActiveCell.FormulaR1C1 = "FEB"
> > * *Range("A3").Select
> > * *Selection.ClearContents
> > * *Range("A2").Select
> > * *ActiveCell.FormulaR1C1 = "FEB"
> > * *Range("A3").Select
> > * *ActiveCell.FormulaR1C1 = "MAR"
> > * *Range("A1:A3").Select
> > * *Selection.AutoFill Destination:=Range("A1:A12"), Type:=xlFillDefault
> > * *Range("A1:A12").Select
> > * *Range("B1").Select
> > * *ActiveCell.FormulaR1C1 = "1"
> > * *Range("B2").Select
> > * *ActiveCell.FormulaR1C1 = "2"
> > * *Range("B3").Select
> > * *ActiveCell.FormulaR1C1 = "3"
> > * *Range("B1:B3").Select
> > * *Selection.AutoFill Destination:=Range("B1:B12"), Type:=xlFillDefault
> > * *Range("B1:B12").Select
> > * *Sheets("Sheet1").Select
> > * *Columns("A:C").Select
> > * *Range("C1").Activate
> > * *Selection.ColumnWidth = 8
> > * *Range("D2").Select
> > * *ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet2!C[-3]:C[-2],2,FALSE)"
> > * *Selection.AutoFill Destination:=Range("D216"), Type:=xlFillDefault
> > * *Range("D216").Select
> > * *Columns("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").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("D216")
> > * *Range("D216").Select
> > * *Columns("D").Select
> > * *Selection.Copy
> > * *Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > SkipBlanks _
> > * * * *:=False, Transpose:=False
> > * *Application.CutCopyMode = False
> > * *Columns("A:C").Select
> > * *Range("C1").Activate
> > * *Selection.Delete Shift:=xlToLeft
> > * *Columns("A:M").Select
> > * *Selection.ColumnWidth = 12.86
> > * *Range("A1").Select
> > * *ActiveCell.FormulaR1C1 = "DATE"
> > * *Range("B1").Select
> > * *ActiveCell.FormulaR1C1 = "TIME"
> > * *Range("C1").Select
> > * *ActiveCell.FormulaR1C1 = "INBOUND"
> > * *Range("D1").Select
> > * *ActiveCell.FormulaR1C1 = "AVG INBOUND TIME"
> > * *Range("E1").Select
> > * *ActiveCell.FormulaR1C1 = "ABAND"
> > * *Range("F1").Select
> > * *ActiveCell.FormulaR1C1 = "AVG ABAND TIME"
> > * *Range("G1").Select
> > * *ActiveCell.FormulaR1C1 = "AVG TALK TIME"
> > * *Columns("H:J").Select
> > * *Selection.Delete Shift:=xlToLeft
> > * *Range("H1").Select
> > * *ActiveCell.FormulaR1C1 = "TOTAL INTERNAL"
> > * *Range("I1").Select
> > * *ActiveCell.FormulaR1C1 = "AVG STAFF"
> > * *Range("J1").Select
> > * *ActiveCell.FormulaR1C1 = "% IN SERV LEVEL"
> > * *Cells.Select
> > * *With Selection
> > * * * *.HorizontalAlignment = xlLeft
> > * * * *.VerticalAlignment = xlBottom
> > * * * *.WrapText = False
> > * * * *.Orientation = 0
> > * * * *.AddIndent = False
> > * * * *.IndentLevel = 0
> > * * * *.ShrinkToFit = False
> > * * * *.ReadingOrder = xlContext
> > * * * *.MergeCells = False
> > * *End With
> > * *Cells.EntireColumn.AutoFit
> > * *Range("A2").Select
> > * *Application.DisplayAlerts = False
> > * *ActiveWorkbook.SaveAs Filename:="y:\bcms_skill1.xlsm", FileFormat:= _
> > * * * *xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
> > * * * *Application.DisplayAlerts = True
>
> > * * * *Exit Sub
> > Err_Trap:
> > * *Debug.Assert Err <> 0
> > * * * *Err.Clear
> > * * * *Resume Next
> > End Sub
>
> > Cheers
> > Shasur
>
> > --
> >http://vbadud.blogspot.com
>
> > "Jon Dibble" wrote:
>
> >> Hi
> >> Am very new to VB and am having trouble with this Macro. I f anyone could
> >> help it would be really appreciated.
>
> >> Sub bcms()
> >> '
> >> '
> >> '
>
> >> Set NewSht = ThisWorkbook.ActiveSheet
> >> * * ChDir "Y:\"
> >> * * Set CSVFile =
> >> Workbooks.Open(Filename:="Y:\report_list_bcms_skill_1_.csv")
> >> * * CSVFile.ActiveSheet.Range("A1:U20").Copy _
> >> * * Destination:=NewSht.Range("A1")
> >> * * Application.CutCopyMode = False
> >> * * Cells.Select
> >> * * Cells.EntireColumn.AutoFit
> >> * * Columns("A:B").Select
> >> * * Range("B1").Activate
> >> * * Selection.Delete Shift:=xlToLeft
> >> * * Columns("B:G").Select
> >> * * Selection.Delete Shift:=xlToLeft
> >> * * Columns("C:E").Select
> >> * * Selection.Insert Shift:=xlToRight,
> >> CopyOrigin:=xlFormatFromLeftOrAbove
> >> * * Rows("19:20").Select
> >> * * Selection.Delete Shift:=xlUp
> >> * * Range("B4:B18").Select Selection.TextToColumns
> >> Destination:=Range("B4"),
> >> DataType:=xlDelimited,TextQualifier:=xlDoubleQuote,ConsecutiveDelimiter:=Fa*lse,
> >> Tab:=True, _
> >> * * * * Semicolon:=False, Comma:=False, Space:=False, Other:=True,
> >> OtherChar
> >> _
> >> * * * * :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
> >> TrailingMinusNumbers:=True
> >> * * Columns("C:C").Select
> >> * * Selection.Delete Shift:=xlToLeft
> >> * * Columns("C").Select
> >> * * Selection.Delete Shift:=xlToLeft
> >> * * Rows("1:3").Select
> >> * * Range("A3").Activate
> >> * * Selection.Delete Shift:=xlUp
> >> * * Rows("1:1").Select
> >> * * Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
> >> * * Columns("B:G").Select
> >> * * Selection.Insert Shift:=xlToRight,
> >> CopyOrigin:=xlFormatFromLeftOrAbove
> >> * * Selection.ColumnWidth = 10
> >> * * Range("A2:A16").Select
> >> * * Selection.Replace What:=",", Replacement:="", LookAt:=xlPart, _
> >> * * * * SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
> >> * * * * ReplaceFormat:=False
> >> * * Selection.TextToColumns Destination:=Range("A2"),
> >> DataType:=xlFixedWidth, _
> >> * * * * OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(5, 1),
> >> Array(8,
> >> 1), Array(13, _
> >> * * * * 1), Array(17, 1), Array(19, 1)), TrailingMinusNumbers:=True
> >> * * Columns("A:C").Select
> >> * * Range("C1").Activate
> >> * * Selection.Delete Shift:=xlToLeft
> >> * * Range("A2").Select
> >> * * Sheets("Sheet2").Select
> >> * * ActiveCell.FormulaR1C1 = "JAN"
> >> * * Range("A3").Select
> >> * * ActiveCell.FormulaR1C1 = "FEB"
> >> * * Range("A3").Select
> >> * * Selection.ClearContents
> >> * * Range("A2").Select
> >> * * ActiveCell.FormulaR1C1 = "FEB"
> >> * * Range("A3").Select
> >> * * ActiveCell.FormulaR1C1 = "MAR"
> >> * * Range("A1:A3").Select
> >> * * Selection.AutoFill Destination:=Range("A1:A12"), Type:=xlFillDefault
> >> * * Range("A1:A12").Select
> >> * * Range("B1").Select
> >> * * ActiveCell.FormulaR1C1 = "1"
> >> * * Range("B2").Select
> >> * * ActiveCell.FormulaR1C1 = "2"
> >> * * Range("B3").Select
> >> * * ActiveCell.FormulaR1C1 = "3"
> >> * * Range("B1:B3").Select
> >> * * Selection.AutoFill Destination:=Range("B1:B12"), Type:=xlFillDefault
> >> * * Range("B1:B12").Select
> >> * * Sheets("Sheet1").Select
> >> * * Columns("A:C").Select
> >> * * Range("C1").Activate
> >> * * Selection.ColumnWidth = 8
> >> * * Range("D2").Select
> >> * * ActiveCell.FormulaR1C1 =
> >> "=VLOOKUP(RC[-3],Sheet2!C[-3]:C[-2],2,FALSE)"
> >> * * Selection.AutoFill Destination:=Range("D216"),
>
> ...
>
> læs mere »- Skjul tekst i anførselstegn -
>
> - Vis tekst i anførselstegn -
 
Reply With Quote
 
Bob Phillips
Guest
Posts: n/a
 
      8th Jan 2009
Try re-formatting

Selection.TextToColumns.Destination:=Range("A2"), _
DataType:=xlFixedWidth, _
OtherChar:="-", _
FieldInfo:=Array(Array(0, 1), Array(5, 1), _
Array(8,1), Array(13, 1), _
Array(17, 1), Array(19, 1)), _
TrailingMinusNumbers:=True

And

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

And

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


--
__________________________________
HTH

Bob

"Jon Dibble" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Thanks Bob thats fixed the first code error
>
> But still having issues with
>
> Selection.TextToColumns.Destination:=Range("A2"),DataType:=xlFixedWidth, _
> OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(5, 1),
> Array(8,1), Array(13, _
> 1), Array(17, 1), Array(19, 1)), TrailingMinusNumbers:=True
>
> And
>
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
>
> And
>
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
>
>
>
>
>
> "Bob Phillips" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Ithin k it is this line
>>
>>
>> Range("B4:B18").Select Selection.TextToColumns
>> Destination:=Range("B4"),
>> DataType:=xlDelimited,TextQualifier:=xlDoubleQuote,ConsecutiveDelimiter:=False,
>> Tab:=True, _
>> Semicolon:=False, Comma:=False, Space:=False, Other:=True,
>> OtherChar
>> _
>> :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
>> TrailingMinusNumbers:=True
>>
>> change it to
>>
>> Range("B4:B18").TextToColumns Destination:=Range("B4"), _
>> DataType:=xlDelimited, _
>> TextQualifier:=xlDoubleQuote, _
>> ConsecutiveDelimiter:=False, _
>> Tab:=True, _
>> Semicolon:=False, _
>> Comma:=False, _
>> Space:=False, _
>> Other:=True, _
>> OtherChar:="-", _
>> FieldInfo:=Array(Array(1, 1), Array(2,
>> 1)), _
>> TrailingMinusNumbers:=True
>>
>> --
>> __________________________________
>> HTH
>>
>> Bob
>>
>> "Jon Dibble" <(E-Mail Removed)> wrote in message
>> news:40631FD6-BF7C-4F7E-9BFA-(E-Mail Removed)...
>>> Hi
>>> Am very new to VB and am having trouble with this Macro. I f anyone
>>> could help it would be really appreciated.
>>>
>>> Sub bcms()
>>> '
>>> '
>>> '
>>>
>>> Set NewSht = ThisWorkbook.ActiveSheet
>>> ChDir "Y:\"
>>> Set CSVFile =
>>> Workbooks.Open(Filename:="Y:\report_list_bcms_skill_1_.csv")
>>> CSVFile.ActiveSheet.Range("A1:U20").Copy _
>>> Destination:=NewSht.Range("A1")
>>> Application.CutCopyMode = False
>>> Cells.Select
>>> Cells.EntireColumn.AutoFit
>>> Columns("A:B").Select
>>> Range("B1").Activate
>>> Selection.Delete Shift:=xlToLeft
>>> Columns("B:G").Select
>>> Selection.Delete Shift:=xlToLeft
>>> Columns("C:E").Select
>>> Selection.Insert Shift:=xlToRight,
>>> CopyOrigin:=xlFormatFromLeftOrAbove
>>> Rows("19:20").Select
>>> Selection.Delete Shift:=xlUp
>>> Range("B4:B18").Select Selection.TextToColumns
>>> Destination:=Range("B4"),
>>> DataType:=xlDelimited,TextQualifier:=xlDoubleQuote,ConsecutiveDelimiter:=False,
>>> Tab:=True, _
>>> Semicolon:=False, Comma:=False, Space:=False, Other:=True,
>>> OtherChar
>>> _
>>> :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
>>> TrailingMinusNumbers:=True
>>> Columns("C:C").Select
>>> Selection.Delete Shift:=xlToLeft
>>> Columns("C").Select
>>> Selection.Delete Shift:=xlToLeft
>>> Rows("1:3").Select
>>> Range("A3").Activate
>>> Selection.Delete Shift:=xlUp
>>> Rows("1:1").Select
>>> Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
>>> Columns("B:G").Select
>>> Selection.Insert Shift:=xlToRight,
>>> CopyOrigin:=xlFormatFromLeftOrAbove
>>> Selection.ColumnWidth = 10
>>> Range("A2:A16").Select
>>> Selection.Replace What:=",", Replacement:="", LookAt:=xlPart, _
>>> SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
>>> ReplaceFormat:=False
>>> Selection.TextToColumns Destination:=Range("A2"),
>>> DataType:=xlFixedWidth, _
>>> OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(5, 1),
>>> Array(8,
>>> 1), Array(13, _
>>> 1), Array(17, 1), Array(19, 1)), TrailingMinusNumbers:=True
>>> Columns("A:C").Select
>>> Range("C1").Activate
>>> Selection.Delete Shift:=xlToLeft
>>> Range("A2").Select
>>> Sheets("Sheet2").Select
>>> ActiveCell.FormulaR1C1 = "JAN"
>>> Range("A3").Select
>>> ActiveCell.FormulaR1C1 = "FEB"
>>> Range("A3").Select
>>> Selection.ClearContents
>>> Range("A2").Select
>>> ActiveCell.FormulaR1C1 = "FEB"
>>> Range("A3").Select
>>> ActiveCell.FormulaR1C1 = "MAR"
>>> Range("A1:A3").Select
>>> Selection.AutoFill Destination:=Range("A1:A12"), Type:=xlFillDefault
>>> Range("A1:A12").Select
>>> Range("B1").Select
>>> ActiveCell.FormulaR1C1 = "1"
>>> Range("B2").Select
>>> ActiveCell.FormulaR1C1 = "2"
>>> Range("B3").Select
>>> ActiveCell.FormulaR1C1 = "3"
>>> Range("B1:B3").Select
>>> Selection.AutoFill Destination:=Range("B1:B12"), Type:=xlFillDefault
>>> Range("B1:B12").Select
>>> Sheets("Sheet1").Select
>>> Columns("A:C").Select
>>> Range("C1").Activate
>>> Selection.ColumnWidth = 8
>>> Range("D2").Select
>>> ActiveCell.FormulaR1C1 =
>>> "=VLOOKUP(RC[-3],Sheet2!C[-3]:C[-2],2,FALSE)"
>>> Selection.AutoFill Destination:=Range("D216"), Type:=xlFillDefault
>>> Range("D216").Select
>>> Columns("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").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("D216")
>>> Range("D216").Select
>>> Columns("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
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>
 
Reply With Quote
 
 
 
Reply

« Pivot Table refresh in VBA code not working | Can macro test fill color in cell with conditional format? »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
complie error SDDzuro Microsoft Excel New Users 0 9th Dec 2007 11:12 PM
Complie error Decreenisi Microsoft Excel Programming 2 6th Dec 2007 09:26 AM
Complie Error Help JMay Microsoft Excel Programming 4 24th Jan 2005 04:33 AM
complie error =?Utf-8?B?YnJpYW4=?= Microsoft Excel Programming 2 13th Dec 2004 06:51 PM
Complie Error mohan Microsoft Excel Misc 1 13th Jul 2004 07:53 PM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 03:21 PM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top