Pivot Table

[bmontoni]

Hi All,

I am trying to write a script that creates a pivot from a dataset that may in the future contain a different number of rows. Is there a way I could add in the "last.row" function into this so it creates the pivot from the entire data set even if the amount of records change? Instead I would be open to creating a table from the dataset and reference that table in the pivot.

Sub prepareMBEWpivot()
'
' prepareMBEWpivot Macro
'

'
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R1048576C7", Version:=7).CreatePivotTable TableDestination:= _
"Sheet1!R1C10", TableName:="PivotTable4", DefaultVersion:=7
Sheets("Sheet1").Select
Cells(1, 10).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("generic")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Article")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Valuation Area")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("Standard price"), "Sum of Standard price", xlSum
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Standard price")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable4").PivotFields("Article").Subtotals = Array _
(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable4").PivotFields("generic").Subtotals = Array _
(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable4").PivotFields("Valuation Area").Subtotals _
= Array(False, False, False, False, False, False, False, False, False, False, False, False _
)
ActiveSheet.PivotTables("PivotTable4").PivotFields("Standard price").Subtotals _
= Array(False, False, False, False, False, False, False, False, False, False, False, False _
)
ActiveSheet.PivotTables("PivotTable4").PivotFields("Planned price 1"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
ActiveSheet.PivotTables("PivotTable4").PivotFields("Planned price 2"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
ActiveSheet.PivotTables("PivotTable4").PivotFields("Planned price 3"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
With ActiveSheet.PivotTables("PivotTable4")
.ColumnGrand = False
.RowGrand = False
End With
ActiveSheet.PivotTables("PivotTable4").RowAxisLayout xlTabularRow
ActiveSheet.PivotTables("PivotTable4").RepeatAllLabels xlRepeatLabels
End Sub

 

[RoryA]

Please do not post the same question more than once. Thread closed.