Forum Discussion

Eqa33's avatar
Eqa33
Brass Contributor
Dec 20, 2019
Solved

Using a macro to run a pivot Table but would like the pivot table to appear at the end of the data

Each lot of data used has a different number of rows of information so that I would like the pivot table to appear 2 cells down from the last row of information. How can I make the destination postion flexible in the macro yet always in column A , 2 cells away from last piece of information.

Thanks in advance.

 

I see 75 people have looked at his but no replies. Is that because they don't understand what I am asking?

Macros and VBA
  • Eqa33's avatar
    Eqa33
    Jan 10, 2020

    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.

9 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Dec 21, 2019

    Eqa33 

    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.

     

    • Eqa33's avatar
      Eqa33
      Brass Contributor
      Jan 08, 2020

      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

       

      Riny_van_Eekelen 

Resources