Pivot Table

[LB79]

Hi - does anyone know a piece of code that will change the range my pivot table reads from?

Thanks

[MarkM]

You can use the offset function to accomplish this. There is a lot of good
help on this throughout the discussion group and on Debra’s site
http://www.contextures.com (where I first learned about it)

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),2)

-The COUNTA(Sheet1!$A:$A) will adjust the number of rows for the table as
new data is added.
- You can change the 2 to the number of columns in your pivot table source
data. For example if your data had 15 columns in it then change the 2 to 15.

Optionally, if the number of columns will also change then change the 2 to
this COUNTA(Sheet1!$1:$1).

Hope this helps.


"LB79" wrote:

>
> Hi - does anyone know a piece of code that will change the range my
> pivot table reads from?
>
> Thanks
>
>
> --
> LB79
> ------------------------------------------------------------------------
> LB79's Profile: http://www.excelforum.com/member.php...o&userid=12156
> View this thread: http://www.excelforum.com/showthread...hreadid=562856
>
>

[MarkM]

Sorry, the full link to get to the correct page
is:http://www.contextures.com/xlNames01.html

"MarkM" wrote:

> You can use the offset function to accomplish this. There is a lot of good
> help on this throughout the discussion group and on Debra’s site
> http://www.contextures.com (where I first learned about it)
>
> =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),2)
>
> -The COUNTA(Sheet1!$A:$A) will adjust the number of rows for the table as
> new data is added.
> - You can change the 2 to the number of columns in your pivot table source
> data. For example if your data had 15 columns in it then change the 2 to 15.
>
> Optionally, if the number of columns will also change then change the 2 to
> this COUNTA(Sheet1!$1:$1).
>
> Hope this helps.
>
>
> "LB79" wrote:
>
> >
> > Hi - does anyone know a piece of code that will change the range my
> > pivot table reads from?
> >
> > Thanks
> >
> >
> > --
> > LB79
> > ------------------------------------------------------------------------
> > LB79's Profile: http://www.excelforum.com/member.php...o&userid=12156
> > View this thread: http://www.excelforum.com/showthread...hreadid=562856
> >
> >

[LB79]

Thanks for that formular.
Do you know if there is a way to do it in VBA?

[MarkM]

I am sure there is, I don't know much about VBA. I have just started to
learn a little bit about VBA myself. You may want to try posting this in the
programming group.

"LB79" wrote:

>
> Thanks for that formular.
> Do you know if there is a way to do it in VBA?
>
>
> --
> LB79
> ------------------------------------------------------------------------
> LB79's Profile: http://www.excelforum.com/member.php...o&userid=12156
> View this thread: http://www.excelforum.com/showthread...hreadid=562856
>
>