Pivot table range issue

[Fletchmeister]

I have a sheet that contains data that keep getting updated weekly. As this data keeps changing I am looking for a way for a pivot table to contsantly update with the new data. The way below is to create a new tabel each time the data is uodated. However where is references cell R21721 below, I am wanting this to change with the data. So if next time the data is updated the row is 22600 the formula will update for the table.

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"data!R1C1:R21721C72").CreatePivotTable TableDestination:=Range("A2"), _
TableName:="PivotTable2"

Hope i'm making sense!

Fletch

 

[Fletchmeister]

Sorry made a mistake on that it only shows one office in the list. There should be Sheffield, Manchester, Cardiff & Edinburgh taken from the title of office but it only wants to show Sheffield.

Fletch

 

[Fletchmeister]

Actually it only shows 1 office not all 4 any ideas?

Fletch

 

[Aladin Akyurek]

Actually it only shows 1 office not all 4 any ideas?

Fletch

Are these in column A

00505748001
00503534001
00503578001
00503625001
00503662001
00503693001
00503732001
00503734001

the so-called Ref No's?

 

[Fletchmeister]

Yeah these are in column A exactly as I have coppied them. I've got to say i'm really impressed with the working out so far. It is nearly working i'm just trying to figure out how!!

Thanks

Fletch

 

[Aladin Akyurek]

Yeah these are in column A exactly as I have coppied them. I've got to say i'm really impressed with the working out so far. It is nearly working i'm just trying to figure out how!!

Thanks

Fletch

But these things are not real numbers. So replace

9.99999999999999E+307

with

REPT("z",255)

in B2 on Admin.

 

[Fletchmeister]

I have no idea where "The Hague" is unfortunately but where ever it is you are a star!

Thank you very much it works fantastic.

Fletch

 

[Fletchmeister]

I've hit a bit of a snag with this and I hope it's only a small problem but when I run the routine to update the data the reference where we defined the name that reads =OFFSET(Source!$A$1,0,0............ it replaces the $A$1 with #REF. Any ideas on how to stop this?

Thanks

Fletch

 

[Aladin Akyurek]

I've hit a bit of a snag with this and I hope it's only a small problem but when I run the routine to update the data the reference where we defined the name that reads =OFFSET(Source!$A$1,0,0............ it replaces the $A$1 with #REF. Any ideas on how to stop this?

Thanks

Fletch

Fletch,

Are you running a macro or something to update the data?

If so and the routine really does not change where the data starts, try...

=OFFSET(INDEX($A:$A,1),...)

where the ... bit is exactly the same as before.

 

[GorD]

Guys, I am a new member who has been browsing this site for the last week or so and am most impressed with the knowledge that seems to be here.
Sorry for reopening this issue after you seem to have come to a satisfactory conclusion but this may be another solution if I have read the original question correctly.

Goto the insert, name , define, menu and in the refers to box type the following formula:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1)) and name it something like rngPivot. (assumes data starts in cell a1)

Then in the pivot table wizard, when you define the range, use the insert, name, paste menu and paste the name you just created.
This will then select all records including any added and updating the pivot table is just a matter of refreshing the data. (as long as there are no blanks in the data in col a).

I suspect I have over simplified the question and this is not new to you but I thought I would post it just in case