Copy PIVOT table?

[MikeF]

Have a nicely-constructed Pivot Table that runs off Sheet ACC / Range
d7:x2000 every week.

There are numerous weekly files [well, 52 per year!].
The sheet and field names are exactly the same.
.... Just the data is different each week.

Have attempted numerous methods of copying/copy worksheet/and so on into
each week, then using Pivot Table / Options / Change Data Source, but to no
avail.

Any assistance will be sincerely appreciated.
- Mike

 

[Barb Reinhardt]

I set up a dynamic range for pivot table sources that expands/contracts as
needed.

Let's say row 1 has a header and the data is in subsequent rows (with no
empty rows).

Define the range this way

=Offset(Sheet1!A1,1,0,counta(Sheet1!A:A)-1,counta(sheet1!1:1))

And set the source to the defined range.

HTH,
Barb Reinhardt

 

[MikeF]

Barb,
Thanx for the reply.

My pivot table actually starts at d7 [there is some titling/etc from other
parts of the workbook], and ends at column x.
So I modified your formula as follows, but it doesnt' work ...

=OFFSET(acc!$D$7,0,0,COUNTA(acc!$D$7:$D$2000),COUNTA(acc!$4:$24))

*** Tried your solution with everything starting in d1, it does work.

Regards,
-Mike

I set up a dynamic range for pivot table sources that expands/contracts as
needed.

Let's say row 1 has a header and the data is in subsequent rows (with no
empty rows).

Define the range this way

=Offset(Sheet1!A1,1,0,counta(Sheet1!A:A)-1,counta(sheet1!1:1))

And set the source to the defined range.

HTH,
Barb Reinhardt

Have a nicely-constructed Pivot Table that runs off Sheet ACC / Range
d7:x2000 every week.

There are numerous weekly files [well, 52 per year!].
The sheet and field names are exactly the same.
... Just the data is different each week.

Have attempted numerous methods of copying/copy worksheet/and so on into
each week, then using Pivot Table / Options / Change Data Source, but to no
avail.

Any assistance will be sincerely appreciated.
- Mike

 

[Barb Reinhardt]

This is what you have:

=OFFSET(acc!$D$7,0,0,COUNTA(acc!$D$7:$D$2000),COUNTA(acc!$4:$24))

I'd tweek a couple of sections.

Change CountA(acc!$D7$2000) to

Counta(Acc!$D:$D) and subtrack the number of rows you don't want to
include. You may need to play with this a bit.

Change this; COUNTA(acc!$4:$24))

to something like
COUNTA(Acc!$7:$7) and subtract the number of columns that may have data you
don't want to include.

It's a bit trial by error. Once you get the handle on Offset, you'll use it
all the time.

HTH,
Barb Reinhardt

Barb,
Thanx for the reply.

My pivot table actually starts at d7 [there is some titling/etc from other
parts of the workbook], and ends at column x.
So I modified your formula as follows, but it doesnt' work ...

=OFFSET(acc!$D$7,0,0,COUNTA(acc!$D$7:$D$2000),COUNTA(acc!$4:$24))

*** Tried your solution with everything starting in d1, it does work.

Regards,
-Mike

I set up a dynamic range for pivot table sources that expands/contracts as
needed.

Let's say row 1 has a header and the data is in subsequent rows (with no
empty rows).

Define the range this way

=Offset(Sheet1!A1,1,0,counta(Sheet1!A:A)-1,counta(sheet1!1:1))

And set the source to the defined range.

HTH,
Barb Reinhardt

Have a nicely-constructed Pivot Table that runs off Sheet ACC / Range
d7:x2000 every week.

There are numerous weekly files [well, 52 per year!].
The sheet and field names are exactly the same.
... Just the data is different each week.

Have attempted numerous methods of copying/copy worksheet/and so on into
each week, then using Pivot Table / Options / Change Data Source, but to no
avail.

Any assistance will be sincerely appreciated.
- Mike

 

[MikeF]

Barb,

This ended up working ....

=OFFSET(acc!$D$7,0,0,COUNTA(acc!$D$7:$D$2000),COUNTA(acc!$D$7:$X$7))

Thanx again!!
- Mike

This is what you have:

=OFFSET(acc!$D$7,0,0,COUNTA(acc!$D$7:$D$2000),COUNTA(acc!$4:$24))

I'd tweek a couple of sections.

Change CountA(acc!$D7$2000) to

Counta(Acc!$D:$D) and subtrack the number of rows you don't want to
include. You may need to play with this a bit.

Change this; COUNTA(acc!$4:$24))

to something like
COUNTA(Acc!$7:$7) and subtract the number of columns that may have data you
don't want to include.

It's a bit trial by error. Once you get the handle on Offset, you'll use it
all the time.

HTH,
Barb Reinhardt

Barb,
Thanx for the reply.

My pivot table actually starts at d7 [there is some titling/etc from other
parts of the workbook], and ends at column x.
So I modified your formula as follows, but it doesnt' work ...

=OFFSET(acc!$D$7,0,0,COUNTA(acc!$D$7:$D$2000),COUNTA(acc!$4:$24))

*** Tried your solution with everything starting in d1, it does work.

Regards,
-Mike

I set up a dynamic range for pivot table sources that expands/contracts as
needed.

Let's say row 1 has a header and the data is in subsequent rows (with no
empty rows).

Define the range this way

=Offset(Sheet1!A1,1,0,counta(Sheet1!A:A)-1,counta(sheet1!1:1))

And set the source to the defined range.

HTH,
Barb Reinhardt

:


Have a nicely-constructed Pivot Table that runs off Sheet ACC / Range
d7:x2000 every week.

There are numerous weekly files [well, 52 per year!].
The sheet and field names are exactly the same.
... Just the data is different each week.

Have attempted numerous methods of copying/copy worksheet/and so on into
each week, then using Pivot Table / Options / Change Data Source, but to no
avail.

Any assistance will be sincerely appreciated.
- Mike

 

[Barb Reinhardt]

The only issue I'd have with your formula is what happens if the range ever
expands to the right or is longer than 2000? It sometimes happens and you
won't catch it with your pivot source.

Barb,

This ended up working ....

=OFFSET(acc!$D$7,0,0,COUNTA(acc!$D$7:$D$2000),COUNTA(acc!$D$7:$X$7))

Thanx again!!
- Mike

This is what you have:

=OFFSET(acc!$D$7,0,0,COUNTA(acc!$D$7:$D$2000),COUNTA(acc!$4:$24))

I'd tweek a couple of sections.

Change CountA(acc!$D7$2000) to

Counta(Acc!$D:$D) and subtrack the number of rows you don't want to
include. You may need to play with this a bit.

Change this; COUNTA(acc!$4:$24))

to something like
COUNTA(Acc!$7:$7) and subtract the number of columns that may have data you
don't want to include.

It's a bit trial by error. Once you get the handle on Offset, you'll use it
all the time.

HTH,
Barb Reinhardt

Barb,
Thanx for the reply.

My pivot table actually starts at d7 [there is some titling/etc from other
parts of the workbook], and ends at column x.
So I modified your formula as follows, but it doesnt' work ...

=OFFSET(acc!$D$7,0,0,COUNTA(acc!$D$7:$D$2000),COUNTA(acc!$4:$24))

*** Tried your solution with everything starting in d1, it does work.

Regards,
-Mike

:

I set up a dynamic range for pivot table sources that expands/contracts as
needed.

Let's say row 1 has a header and the data is in subsequent rows (with no
empty rows).

Define the range this way

=Offset(Sheet1!A1,1,0,counta(Sheet1!A:A)-1,counta(sheet1!1:1))

And set the source to the defined range.

HTH,
Barb Reinhardt

:


Have a nicely-constructed Pivot Table that runs off Sheet ACC / Range
d7:x2000 every week.

There are numerous weekly files [well, 52 per year!].
The sheet and field names are exactly the same.
... Just the data is different each week.

Have attempted numerous methods of copying/copy worksheet/and so on into
each week, then using Pivot Table / Options / Change Data Source, but to no
avail.

Any assistance will be sincerely appreciated.
- Mike