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
 

Some videos you may like

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

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,291
Office Version
365
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,302
Messages
5,486,058
Members
407,529
Latest member
netojose

This Week's Hot Topics

Top