Pivot Table help

sjp1989

New Member
Joined
Apr 27, 2015
Messages
7
Hi all,

I need help with taking data from a pivot table and generating a data table, I think a macro or VBA would be the best bet.
For example:

This is my Pivot table (sheet 2):

Row labels Sum of invoices Sum of invoices not valid Sum of valid invoices Sum not submitted Sum of total submitted Sum of % submitted
Blue 10 1 9 3 6 67%
Pink 1 0 1 0 1 100%
White 5 0 5 2 3 60%
Grand total 16 1 15 5 10 67%


In another sheet (sheet 1) I want it to create a table like this. the table should label all rows and columns and populate the correct data from that pivot table

# of invoices # Valid # submitted % submitted
White
Blue
Pink
Totals

Can someone help me with this? That would be great, thanks
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Hi & welcome to the board.

This will reference and extract data from a pivot table:
Code:
Sub PivotTables()

    Dim pt As PivotTable
    
    Set pt = ThisWorkbook.Worksheets("Sheet1").PivotTables("PivotTable1")
    
    With ThisWorkbook.Worksheets("Sheet2")
        .Select
        pt.PivotFields("Sum of calls outbound").DataRange.Copy
        .Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    End With


End Sub

Have a look at this site to see how to reference different parts of the pivot table: Referencing Pivot Table Ranges in VBA - Peltier Tech Blog
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

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