VBA Code for creating a table from a pivot.

manonmoon

New Member
Joined
Nov 9, 2015
Messages
1
Hi guys, first time ever posting! i was trying to look through the forums to see if i could find something similar to my needs but no luck. i have also tried to record my own macro but it doesn't work exactly the way i need it to. my pivot current looks like this in the fields:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Agent Name[/TD]
[TD]APPR[/TD]
[TD]CAN[/TD]
[TD]PEND[/TD]
[TD]REJ[/TD]
[TD]SUB[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]Beatty[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Jason[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD]18[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]7[/TD]
[TD][/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]Dave[/TD]
[TD]24[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]14[/TD]
[TD][/TD]
[TD]47[/TD]
[/TR]
</tbody>[/TABLE]
Filters are : Office Name / SalesPeriod
Columns: AppStatusCode
Rows: Agent Full Name
Values: Count of AppStatusCode

i want to be able to pull all data in there and paste it into a table in the same sheet starting in cell I1 to T. the reason i am doing this is because the table has extra columns, like Percent APPR, Percent Cancel, Percent Pending, and so on for each of the status. from this table im able to reorder the list to show agents who have the highest approval rate. Unless there is someway to reorder the data in the pivot to show the percent of each app and be able to reorder from the highest approval rating.
This pivot gets updated every week and i have over 300+ offices i need to do this with. the trouble i came across with the recording macro was that it would also include the grand total row in the pivot and that would mess up the order ranking in the table. if anyone has any tips or advice on how to do this, i would really appreciate any and all help. thank you!

table looks something like this"


[TABLE="width: 500"]
<tbody>[TR]
[TD]Agent name[/TD]
[TD]APPR[/TD]
[TD]CAN[/TD]
[TD]PEND[/TD]
[TD]REJ[/TD]
[TD]SUB[/TD]
[TD]TOTAL[/TD]
[TD]% APPR[/TD]
[TD]% CANCEL[/TD]
[TD]%PEND[/TD]
[TD]% REJ[/TD]
[TD]% SUB[/TD]
[/TR]
[TR]
[TD]beatty[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]100%[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]jason[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]15[/TD]
[TD]66%[/TD]
[TD]0[/TD]
[TD]33%[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
it looks something like that. the % column already has formulas in so the info just needs to be pasted into. once again thank you!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Forum statistics

Threads
1,226,693
Messages
6,192,463
Members
453,725
Latest member
cvsdatreas

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