Hi,

I'm new to the board and have a question on how to make my pivot table work when the data changes on the next run.
Specifically the number of rows and the serial number of the of the unit changes. I think I need a dynamic range but don't know how to do it.
if anyone can help me I would greatly appreciate it.

Here is the code create by the macro recorder.
What changes each time is the serial number "FNM00141100248" and the number of rows. All the pivot table fields remain the same.


Sub Macro2()
'
' Macro2 Macro
' automate
'
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"FNM00141100248!R1C1:R99C11", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion14
Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("BOM FLAG")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("TLA SERIAL NUMBER")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MATERIAL NUMBER")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("SERIAL NUMBER"), "Count of SERIAL NUMBER", xlCount
ActiveSheet.PivotTables("PivotTable1").PivotFields("BOM FLAG").CurrentPage = _
"(All)"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("BOM FLAG")
.PivotItems("Y").Visible = False
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("BOM FLAG"). _
EnableMultiplePageItems = True
End Sub