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]

The current range is A1-BT21721 this includes the titles that are required for the pivot table.

Fletch

 

[Fletchmeister]

Just a thought but I already have my coding setup to create the pivot table with the current range set in place as this -

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

Is it not possible to somehow alter the range details similiar to how I select the maximum range like this -

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

If this can be incorporated into the above code would this not be the easist solution?

Fletch

(Totally out of my depth but learning fast!!)

 

[Aladin Akyurek]

The current range is A1-BT21721 this includes the titles that are required for the pivot table.

Fletch

What is in A, B, etc.? I'm interested in a column that is numeric (dates, numbers, time values).

 

[Fletchmeister]

a = Ref No., b = Surname, c = Office, d = Solicitor Name, e = Code(AlphaNumeric), is this enough?

Fletch

 

[Fletchmeister]

a = Ref No., b = Surname, c = Office, d = Solicitor Name, e = Code(AlphaNumeric), is this enough?

Fletch

 

[Aladin Akyurek]

a = Ref No., b = Surname, c = Office, d = Solicitor Name, e = Code(AlphaNumeric), is this enough?

Fletch

Is Ref No numeric?

 

[Fletchmeister]

Yes that is an individual numeric only reference.

Fletch

 

[Fletchmeister]

These are examples -

00505748001
00503534001
00503578001
00503625001
00503662001
00503693001
00503732001
00503734001


Fletch

 

[Aladin Akyurek]

Yes that is an individual numeric only reference.

Fletch

In what follows I'll refer to the sheet which houses the source data as Source.


( 0.) Insert a worksheet and name it, e.g., Admin.
( 1.) Activate Admin.
( 2.) In A2 enter: # of rows [ which is just a label ]
( 3.) In B2 enter: =MATCH(9.99999999999999E+307,Source!A:A)
( 4.) In A3 enter: # of data columns [ which is just a label ]
( 5.) In B3 enter: =MATCH(REPT("z",255),Source!1:1)
( 6.) Activate Insert|Name|Define.
( 7.) Enter PBase as name in the Names in Worbook box.
( 8.) Enter the following formula in the Refers to box:

=OFFSET(Source!$A$1,0,0,Admin!$B$2,Admin!$B$3)

(11.) Click OK.

Feed PBase as Range in Pivot Tables Wizard.

 

[Fletchmeister]

I think this is really starting to work now fantastic. However when I put the office tab into my pivot using the wizard it only shows one out of 4 offices. All the offices are ticked I have checked this. Any ideas?

Fletch