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