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?

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

Jaybatz wrote:
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?

--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200808/1

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

Jaybatz wrote:
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?

--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200808/1

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

--

Joe Mac


"Jaybatz" wrote:

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?

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

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

Jaybatz wrote:
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?


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com