sravanthi.boggaram
New Member
- Joined
- Nov 9, 2011
- Messages
- 2
The requirement is to write a macro for generating a report in columnar form for checking ticket solving efficiency.. The macro i have generates a new sheet with the report but column A in sheet 1 (report) is in reverse order.. How do i put the last cell value into the first one and so on??
this is the macro:
Pls help me!!
this is the macro:
Code:
Sub format_report()
Application.ScreenUpdating = False
lastrow = Worksheets("iRAM Report").Range("A" & Rows.Count).End(xlUp).Row
Columns("C:C").TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=True, Semicolon _
:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, _
1), TrailingMinusNumbers:=True
Columns("D:D").TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=True, Semicolon _
:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, _
1), TrailingMinusNumbers:=True
Range("E1").Value = "Month"
Range("F1").Value = "Age"
Range("E1:F1").Font.Bold = True
For i = 2 To lastrow
Range("E" & i).FormulaR1C1 = "=TEXT(RC[-2],""mmm"") &"" "" & TEXT(RC[-2],""yyyy"")"
Range("F" & i).Value = Range("D" & i).Value - Range("C" & i).Value + 1
Next i
Columns("E:E").Copy
Columns("E:E").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("E:E").Copy
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Sheet1"
Worksheets("Sheet1").Columns("A:A").Select
ActiveSheet.Paste
Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Columns( _
"A:A"), CopyToRange:=Columns("B:B"), Unique:=True
Columns("A:A").Delete
Worksheets("Sheet1").Range("B1").Value = "Total Resolution Time"
Worksheets("Sheet1").Range("C1").Value = "Total Ticket Closed"
Worksheets("Sheet1").Range("D1").Value = "Avg Resolution Time"
lrow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lrow
With Worksheets("Sheet1")
.Range("B" & i).FormulaR1C1 = "=SUMIF('iRAM Report'!C[3]:C[4],Sheet1!RC[-1],'iRAM Report'!C[4])"
.Range("C" & i).FormulaR1C1 = "=COUNTIF('iRAM Report'!C[2],Sheet1!RC[-2])"
.Range("D" & i).FormulaR1C1 = "=RC[-2]/RC[-1]"
.Range("D" & i).NumberFormat = "0.00"
End With
Next i
With Worksheets("Sheet1")
.Columns("A:D").AutoFit
.Range("A1:D1").Font.Bold = True
With Columns("A:D")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("A:D").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
End With
Application.ScreenUpdating = True
End Sub