How do I extend the range of the data used in a PivotTable?

[Jaybatz]

I make weekly budget reports, and I use a PivotTable to get a basic overview.
Now the problem is that as I extract data weekly, it changes and rows are
added to the original data each time. The PivotTable does not recognize this
and only checks the original field range. How do I change the range, without
having to create a new PivotTable every week?

 

[Mattlynn via OfficeKB.com]

Hi
On the Pivot table, click on the pivot table wizard, slect back, m and
increase the range of the rows. It should just bne the number you need to
change. I would suggest, putting in a row range much bigger than needed, so
it will take into account all future extracts
Thanks
Matt

 

[Mattlynn via OfficeKB.com]

Hi
On the Pivot table, click on the pivot table wizard, select back, and
increase the range of the rows. It should just bne the number you need to
change. I would suggest, putting in a row range much bigger than needed, so
it will take into account all future extracts
Thanks
Matt

 

[Joe Mac]

One Option -
On the Pivot Table definition declare the Range as a Name, for the example
I'm providing I call it BudgetData...

Save this little Macro to the Workbook to dynamically count the rows of the
Budget Data that are added during your weekly refresh of the data...

I made some assumptions here, that the raw data for your budget update would
exist in a worksheet "Budget Data" and that the budget data would start at
the top of the worksheet in cell A1...

Sub RowCount()
Dim RowCount As Long

Range("A1").Select
ActiveWorkbook.Names.Add Name:="DataTop", RefersToR1C1:="='Budget
Data'!R1C1"

RowCount = (Selection.CurrentRegion.Rows.Count)
ActiveWorkbook.Names.Add Name:="BudgetData", RefersToR1C1:= _
"='Budget Data'!R1C1:R" & RowCount & "C8"
End Sub

Run the macro each time you refresh the data... You may want to add a
button on an Assumptions page and assign the macro to it...

Hope this helps

 

[Debra Dalgleish]

You can base the pivot table on a dynamic range, as described here:

http://www.contextures.com/xlPivot01.html