Need Macro code for a call centre report

V

venkateshr

New Member
Joined
Oct 22, 2013
Messages
26
Hi Friends,

I am working on a report which needs a macro code, i tried recording the macro and have attached the code below for your easy reference. need some automated code or advanced code. Can anyone please help me:confused:

Also have attached the data format and the output format.

Sub Venky()
'
' Venky Macro
'
' Keyboard Shortcut: Ctrl+Shift+M
'
Sheets("Data").Select
Range("E5:G5").Select
Selection.Copy
ActiveSheet.Next.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("B4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C/R[-3]C"
Range("B5").Select
ActiveSheet.Previous.Select
Range("H5").Select
Selection.Copy
ActiveSheet.Next.Select
Range("B6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B5").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-3]C-R[1]C"
Range("B7").Select
ActiveCell.FormulaR1C1 = "=R[-2]C/R[-6]C"
Range("B8").Select
Sheets("Data").Select
Range("O5").Select
Selection.Copy
Sheets("Macro").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B9").Select
Sheets("Data").Select
Range("M5").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Previous.Select
Range("K5").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
Range("B10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B11").Select
ActiveSheet.Previous.Select
Range("R5").Select
Application.CutCopyMode = False
Selection.Cut
Application.CutCopyMode = False
Selection.Copy
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Previous.Select
Range("T5").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
Range("B12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Previous.Select
Range("V5").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
Range("B13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Previous.Select
Range("P5").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
Range("B14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C14").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-1]/R[-11]C[-1]"
Range("B14").Select
ActiveCell.FormulaR1C1 = "0:01"
Range("C14").Select
Selection.Copy
Range("B14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C14").Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveWorkbook.Save
Range("E13").Select
ActiveWorkbook.Save
End Sub

Data format:
Date:</SPAN>5/22/2014</SPAN>
Time Zone:</SPAN>US</SPAN>
Split/Skill:</SPAN>Pal_Bpollo_PPP</SPAN>
Time</SPAN>Split/Skill</SPAN>CALLSOFFERED</SPAN>ACD Calls</SPAN>Aban Calls</SPAN>Ans.after Threshold</SPAN>Abn.After Threshold</SPAN>%ABN</SPAN>AHT</SPAN>Extn In Calls</SPAN>Max Delay</SPAN>ANSTIME</SPAN>Avg Speed Ans</SPAN>ABNTIME</SPAN>ACD Time</SPAN>Avg ACD Time</SPAN>ACW Time</SPAN>Avg ACW Time</SPAN>Hold Time</SPAN>Avg Hold Time</SPAN>RINGTIME</SPAN>AUX Time</SPAN>Avail Time</SPAN>Staffed Time</SPAN>Other Time</SPAN>Extn Out Calls</SPAN>Extn Out Time</SPAN>Avg Extn Out Time</SPAN>Avg Extn In Time</SPAN>Flow In</SPAN>Flow Out</SPAN>Held Calls</SPAN>Trans Out</SPAN>MAXINQUEUE</SPAN>MAXSTAFFED</SPAN>
Totals</SPAN>0</SPAN>33</SPAN>33</SPAN>0</SPAN>3</SPAN>0</SPAN>0</SPAN>:08:53</SPAN>0</SPAN>:05:37</SPAN>:16:05</SPAN>:00:29</SPAN>:00:00</SPAN>3:06:32</SPAN>:05:39</SPAN>1:25:01</SPAN>:02:35</SPAN>:21:47</SPAN>:05:27</SPAN>:04:28</SPAN>30:45:18</SPAN>21:58:41</SPAN>63:18:54</SPAN>5:58:54</SPAN>7</SPAN>:54:12</SPAN>:07:45</SPAN>0</SPAN>0</SPAN>0</SPAN>4</SPAN>0</SPAN>16</SPAN>137</SPAN>
12:00</SPAN>-</SPAN>12:30AM</SPAN>Pal_Bpollo_PPP</SPAN>1</SPAN>1</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>:03:49</SPAN>0</SPAN>:00:08</SPAN>:00:08</SPAN>:00:08</SPAN>:00:00</SPAN>:00:49</SPAN>:00:49</SPAN>:03:00</SPAN>:03:00</SPAN>:00:00</SPAN>0</SPAN>:00:08</SPAN>:00:19</SPAN>:59:36</SPAN>1:03:52</SPAN>:00:00</SPAN>0</SPAN>:00:00</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>1</SPAN>3</SPAN>
12:30</SPAN>-</SPAN>1:00AM</SPAN>Pal_Bpollo_PPP</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>:00:00</SPAN>:00:00</SPAN>0</SPAN>:00:00</SPAN>:00:00</SPAN>0</SPAN>:00:00</SPAN>0</SPAN>:00:00</SPAN>0</SPAN>:00:00</SPAN>:08:31</SPAN>1:21:29</SPAN>1:30:00</SPAN>:00:00</SPAN>0</SPAN>:00:00</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>3</SPAN>
1:00</SPAN>-</SPAN>1:30AM</SPAN>Pal_Bpollo_PPP</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>:00:00</SPAN>:00:00</SPAN>0</SPAN>:00:00</SPAN>:01:17</SPAN>0</SPAN>:00:00</SPAN>0</SPAN>:00:00</SPAN>0</SPAN>:00:00</SPAN>:01:34</SPAN>1:26:56</SPAN>1:30:00</SPAN>:00:00</SPAN>0</SPAN>:00:00</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>1</SPAN>3</SPAN>
1:30</SPAN>-</SPAN>2:00AM</SPAN>Pal_Bpollo_PPP</SPAN>2</SPAN>2</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>:06:50</SPAN>0</SPAN>:00:06</SPAN>:00:12</SPAN>:00:06</SPAN>:00:00</SPAN>:28:27</SPAN>:04:26</SPAN>:04:59</SPAN>:02:24</SPAN>:00:00</SPAN>0</SPAN>:00:12</SPAN>:02:28</SPAN>:39:53</SPAN>1:30:00</SPAN>:13:52</SPAN>0</SPAN>:00:00</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>1</SPAN>3</SPAN>
2:00</SPAN>-</SPAN>2:30AM</SPAN>Pal_Bpollo_PPP</SPAN>2</SPAN>2</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>:31:02</SPAN>0</SPAN>:00:13</SPAN>:00:22</SPAN>:00:11</SPAN>:00:00</SPAN>:17:56</SPAN>:19:24</SPAN>:05:48</SPAN>:03:00</SPAN>:17:15</SPAN>:17:15</SPAN>:00:22</SPAN>:31:21</SPAN>:57:24</SPAN>1:55:52</SPAN>:03:23</SPAN>0</SPAN>:00:00</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>1</SPAN>0</SPAN>0</SPAN>4</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL span=35></COLGROUP>


Output format:
Calls Offered </SPAN>33</SPAN>
Calls Answered</SPAN>33</SPAN>
Abandoned Calls</SPAN>0</SPAN>
Abandonment Rate %</SPAN>0%</SPAN>
No of calls answered <= 120 Seconds</SPAN>30</SPAN>
No of Calls answered >= 120 Seconds</SPAN>3</SPAN>
Service Level%</SPAN>91%</SPAN>
Average Speed Of Answer (min)</SPAN>:00:29</SPAN>
Max. Answer Delay (min)</SPAN>:05:37</SPAN>
Average Handle Time</SPAN>:08:53</SPAN>
Average Talk Time</SPAN>:05:39</SPAN>
Average ACW Time</SPAN>:02:35</SPAN>
Average Hold Time</SPAN>:05:27</SPAN>
Average Abandoned Time</SPAN>#DIV/0!</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>

Thanks in advance for your time and effort.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
You must log in or register to reply here.

Similar threads

B
  • Solved
vba code to give next Sunday date for pasted data
Replies
2
Views
175
bh24524
B
J
  • Question
Outlook Hypperlink of fila in email body will not open
Replies
1
Views
415
josros60
J
P
  • Solved
Counter in For Each loop
Replies
2
Views
184
Possum17
P
A
  • Question
Macro to update list to last row on another sheet copy source from multiple sheets
Replies
0
Views
298
Azamamir
A
P
  • Question
Help with Vba Code
Replies
3
Views
146
Coyotex3
C

Forum statistics

Threads
1,214,606
Messages
6,120,485
Members
448,967
Latest member
visheshkotha

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top