Hi All,
I am trying to write a script to create a pivot table for the entire data set on a tab. When I record it and run the macro on a table with a larger number of rows, the macro set the range to only include the number of rows that existed in the dataset when the pivot was recorded. Can someone please provide me the script to create this pivot using the "last row" function for columns A through F?
I am trying to write a script to create a pivot table for the entire data set on a tab. When I record it and run the macro on a table with a larger number of rows, the macro set the range to only include the number of rows that existed in the dataset when the pivot was recorded. Can someone please provide me the script to create this pivot using the "last row" function for columns A through F?
Rich (BB code):
Sub prepareMBEWpivot()
'
' prepareMBEWpivot Macro
'
'
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R346C7", Version:=7).CreatePivotTable TableDestination:= _
"Sheet1!R1C10", TableName:="PivotTable1", DefaultVersion:=7
Sheets("Sheet1").Select
Cells(1, 10).Select
With ActiveSheet.PivotTables("PivotTable1")
.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("PivotTable1").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
With ActiveSheet.PivotTables("PivotTable1").PivotFields("generic")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Article")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Valuation Area")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Standard price"), "Sum of Standard price", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Standard price")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Article").Subtotals = Array _
(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("generic").Subtotals = Array _
(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Valuation Area").Subtotals _
= Array(False, False, False, False, False, False, False, False, False, False, False, False _
)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Standard price").Subtotals _
= Array(False, False, False, False, False, False, False, False, False, False, False, False _
)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Planned price 1"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Planned price 2"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Planned price 3"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
With ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = False
.RowGrand = False
End With
ActiveSheet.PivotTables("PivotTable1").RowAxisLayout xlTabularRow
ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
End Sub
Last edited by a moderator: