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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

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