+ Reply to Thread
Results 1 to 2 of 2

Macro to copy Pivot Table information to another tab

  1. #1
    Registered User
    Join Date
    08-18-2012
    Location
    Mexico
    MS-Off Ver
    Excel 2003, Excel 2010
    Posts
    1

    Macro to copy Pivot Table information to another tab

    Hello,


    I have a pivot table that I have to filter, for example sales for products A, B and C for the next three months 7, 8, 9


    Tab "PV"

    Week 7 *Values are not in their corresponding position, they are lightly moved to the left, total is marked with an x.

    A B C TOTAL (x)
    Product 1 100 200 300 X
    Product 2 400 500 600 X
    Product 3 700 800 900 X
    Product 4 1000 1200 1400 X
    Product 5 1600 1800 2000 X


    Week 8 (only B & C products were sold)

    B C TOTAL (x)
    Product 1 200 300 X
    Product 2 500 600 X
    Product 3 800 900 X
    Product 4 1200 1400 X
    Product 5 1800 2000 X

    Week 9 (again all products had good sales ; )

    A B C TOTAL (x)
    Product 1 100 200 300 X
    Product 2 400 500 600 X
    Product 3 700 800 900 X
    Product 4 1000 1200 1400 X
    Product 5 1600 1800 2000 X


    And I have to paste those sales in another Tab named "Sales Results"

    For example in that tab I have:

    Current Week Results for: Week 9

    A B C TOTAL (x)
    Product 1 100 200 300 X
    Product 2 400 500 600 X
    Product 3 700 800 900 X
    Product 4 1000 1200 1400 X
    Product 5 1600 1800 2000 X


    But every week they can change and the sales could be for one, two or the three products and the total will vary so I would have to create a macro that choose specifically for the label (A, B, C or the Total) and then specify in which row to paste it as a special value.

    And also I have a transpose table like this one *The x is again lightly moved to the left but it means the total for each product


    Product 1 Product 2 Product 3 Product 4 Product 5
    TOTAL A X X X X X

    Product 1 Product 2 Product 3 Product 4 Product 5
    TOTAL B X X X X X

    Product 1 Product 2 Product 3 Product 4 Product 5
    TOTAL C X X X X X


    And so on ...


    I have one idea:

    Sub Macro4_obtaindatafromapivottable_and_pasteitanothe rcell()

    ActiveSheet.PivotTables("PivotTable2").PivotSelect "At Risk[All]", _
    xlLabelOnly, True

    Selection.Copy

    'And to paste it in the new cell

    Sheets("PV").Select

    Range("H35").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    '
    End Sub


    But the "At Risk[All]" copies me all the row before that declared value.



    I hope my explanation was clear, please any help is considered.


    Thank you guys,
    Greetings

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Macro to copy Pivot Table information to another tab

    Use Code Tags when posting code & then attach an example workbook
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1