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
Bookmarks