Update PivotTable Range with a button

S

Saariko

Hi,

I have a pivottable which it's range is growing constantly. since I don't
know of an automatic way to increase the range of the table, I thaught of
building some kind of a button in VBA to update the range and relying on the
last row that has data.
I usually ad code to my questions, but not this time. I have no idea how to
start it.

I will be happy for help.

Saariko
 
T

Tom Ogilvy

Make the source of you pivottable a defined name

Insert=>Name=>Define

Name: Database
Refersto:
=offset(Data1!$A$1,0,0,CountA(Data1!$A:$A),10)
Click ADD


change the 10 to the number of columns in your data

Make this defined name the source for your pivot table.
 
M

Mark Parent

Tom:

I often have the same issue as Saariko (original post) and I usually set the
source to an arbitrarily large range to ensure the data never extends beyond
- it seems to work fine. Other than elegance, are there any reasons why
defining the source with a named range as you show is better than simply
defining a very large range?

Thanks for your time!
 
Top