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 postio...
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.

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 

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

    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 

    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      Jan 09, 2020

      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.

       

       

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

         

        Sub Casablanca_2()
'
' Casablanca_2 Macro
'

'
    ActiveWorkbook.Worksheets("Sheet2").PivotTables("PivotTable3").PivotCache. _
        CreatePivotTable TableDestination:="Sheet1!R286C1", TableName:= _
        "PivotTable4", DefaultVersion:=6
    Sheets("Sheet1").Select
    Cells(286, 1).Select
    With ActiveSheet.PivotTables("PivotTable4")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable4").RepeatAllLabels xlRepeatLabels
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Textbox30")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Product_Desc")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Textbox621")
        .Orientation = xlRowField
        .Position = 3
    End With
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("U1"), "Sum of U1", xlSum
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("U2"), "Sum of U2", xlSum
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("U3"), "Sum of U3", xlSum
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("U4"), "Sum of U4", xlSum
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("U7"), "Sum of U7", xlSum
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("U8"), "Sum of U8", xlSum
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("TotalUnits"), "Sum of TotalUnits", xlSum
    Range("B287").Select
    ActiveSheet.PivotTables("PivotTable4").TableStyle2 = "PivotStyleLight15"
    ActiveSheet.PivotTables("PivotTable4").ShowTableStyleRowStripes = True
End Sub

        Riny_van_Eekelen 

Resources