Forum Discussion
Using a macro to run a pivot Table but would like the pivot table to appear at the end of the data
Eqa33 Thanks for your help. I sent you a copy of how the pivot table should look with the subtotal and the custom subtotal. From what I saw of your examples you are right they are not very elegent, nor do I want to do so many field calculations as there are many, many different styles, sizes and colours.
Thanks Eqa.
You indicate that you already have a macro that creates your pivot table. Let's say that your data is on "Sheet1" and starts in cell "A1", the following bit of code should be placed before the code that generates the pivot table.
Dim RowNum As Integer
Dim PtDestination As String
Range("A1").Select
Selection.End(xlDown).Offset(3, 0).Select
RowNum = Selection.Row
PtDestination = "Sheet1!R" & RowNum & "C1"
Then, in the code that generates the pivot table you'll find a bit starting with:
ActiveWorkbook.PivotCaches.Create
After this you'll find a bit like:
TableDestination:= "Sheet1!R100C1"
Now change that part to:
TableDestination:= PtDestination
This should do the trick!
Note that the variables (RowNum and PtDestination) are made up by me. You may replace them by any name you like.
I'm sorry I just saw your reply. Thank you very much for replying. I'm struggling to find where to insert these changes. Can I send you a copy of the macro and you insert them?
Thanks Eqa
Thanks for that. What's the best way to do that? By the way are you able answer my new question I posted about custom subtotals on a pivot table? Thanks EqaRiny_van_Eekelen
Eqa33 In the Visual Basic Editor, locate the macro. Copy the code and paste in in your response. Best to do this by pressing the three dots "..." in the toolbar above and then "</>". This opens a window where you can paste the code. Please give it a try.
Saw your other post. Will respond in a short while.