Update PivotTable Range with a button
 

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

Update PivotTable Range with a button
 

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.

--
Regards,
Tom Ogilvy

"Saariko" wrote in message
...
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

Update PivotTable Range with a button
 

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!



"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy

"Saariko" wrote in message
...
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