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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

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,104
Messages
5,484,700
Members
407,464
Latest member
Halaseh

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top