Forum Discussion
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...
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.
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 SubEqa33 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.