Pivot table range issue

F

Fletchmeister

Board Regular
Joined
May 20, 2003
Messages
114
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 :whistle:
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
try this
Code: 
sheets("Data").activate
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ 
[a1].currentregion.address).CreatePivotTable TableDestination:=Range("A2"), _ 
TableName:="PivotTable2"

It assumes a 'regular' database with one header row
 
Upvote 0
I get the following error when trying to run your code. It appears to fall over at the ConvertR1C1toA1 line.

Compile Error : Sub or function not defined.


Any ideas??

Fletch :confused:
 
Upvote 0
Do you want to create a new Pivot Table each time? Or would you prefer a way to take your existing pivot table and have the Source changed to include the new data appended to the bottom of the Source region?

[Edit] I'm sorry. I deleted that post, because I wanted to check something. And I saw pcc's post and it made me have the questions I asked above[EndEdit]
 
Upvote 0
I'm easy, which ever way is the easiest. I have a pivot table in place so if I can have the code to either update this once the data has changed or the code to create a new one!

I'm finding the pivot table setup on 2000 very frustrating!!

Fletch :rolleyes:
 
Upvote 0
Ok, for me, I like to just set up my pivot just the one time and then scoot on down the road. I just drop each month's new data at the end of my ongoing data sheet, select the sheet with the pivot table, and click a toollbar button that I have customized to run this macro and bang, I'm done and on to my next item. This code makes some assumptions about the data source and current region being one and the same. If that's not the case, you might want to tweak it.

Anyhow, here's the code to extend that Pivot source range. Note, it calls a function, which in turn calls another function, so there's three things here:

<font face=Courier New><SPAN style="color:#007F00">'------------------------------------------------------------------------------</SPAN>
<SPAN style="color:#00007F">Sub</SPAN> ExtendPivotSource()
<SPAN style="color:#007F00">' Written by Greg Truby, 2003</SPAN>
<SPAN style="color:#007F00">' Extends the source data range for a pivot table to be the CurrentRegion</SPAN>
<SPAN style="color:#007F00">' of the range that is currently the source data.  [Allows for quickly</SPAN>
<SPAN style="color:#007F00">' updating reports based on pivot tables derived from datasets that get</SPAN>
<SPAN style="color:#007F00">' appended-to on a monthly basis.]</SPAN>
<SPAN style="color:#007F00">' Future improvement would be to just extend down the columns currently</SPAN>
<SPAN style="color:#007F00">' being used rather than taking the easy way out and using CurrentRegion.</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> pt <SPAN style="color:#00007F">As</SPAN> PivotTable, r <SPAN style="color:#00007F">As</SPAN> Range, a <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
 <SPAN style="color:#00007F">Set</SPAN> pt = ActiveSheet.PivotTables(1)
    a = pt.SourceData
    s = Left(a, InStr(1, a, "!"))
    a = ConvertR1C1toA1(a)
    <SPAN style="color:#00007F">Set</SPAN> r = Range(a)
    <SPAN style="color:#00007F">Set</SPAN> r = r.CurrentRegion
    a = s & r.Address(True, True, xlR1C1)
    pt.SourceData = a
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#007F00">'------------------------------------------------------------------------------</SPAN></FONT>

<font face=Courier New>

<SPAN style="color:#007F00">'------------------------------------------------------------------------------</SPAN>
<SPAN style="color:#00007F">Function</SPAN> ConvertR1C1toA1(strAddress <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#007F00">' Converts an address that is in R1C1 notation to A1 notation</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> strSheet <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> strFirstCell <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strLastCell <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> strCol1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strCol2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> strRow1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strRow2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> intColon <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, intCol1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, intCol2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> intRow1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, intRow2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    
    <SPAN style="color:#00007F">If</SPAN> InStr(1, strAddress, "!") <SPAN style="color:#00007F">Then</SPAN>
        strSheet = Left(strAddress, InStr(1, strAddress, "!"))
        strAddress = Replace(strAddress, strSheet, "", 1)
    <SPAN style="color:#00007F">Else</SPAN>
        strSheet = ""
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    intColon = InStr(1, strAddress, ":")
    intCol1 = InStr(1, strAddress, "C")
    <SPAN style="color:#00007F">If</SPAN> intColon <SPAN style="color:#00007F">Then</SPAN>
        strCol1 = Mid(strAddress, intCol1 + 1, intColon - intCol1 - 1)
    <SPAN style="color:#00007F">Else</SPAN>
        strCol1 = Right(strAddress, Len(strAddress) - intCol1)
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    strRow1 = Mid(strAddress, 2, intCol1 - 2)
    strCol1 = ConvertNumToCol(Val(strCol1))
    ConvertR1C1toA1 = strSheet & strCol1 & strRow1
    <SPAN style="color:#00007F">If</SPAN> intColon = 0 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> Salida
    intCol2 = InStr(intColon, strAddress, "C")
    strCol2 = Mid(strAddress, intCol2 + 1, Len(strAddress) - intCol2)
    strRow2 = Mid(strAddress, intColon + 2, intCol2 - intColon - 2)
    strCol2 = ConvertNumToCol(Val(strCol2))
    ConvertR1C1toA1 = ConvertR1C1toA1 & ":" & strCol2 & strRow2
Salida:
<SPAN style="color:#007F00">'""""""</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>
<SPAN style="color:#007F00">'------------------------------------------------------------------------------</SPAN></FONT>

<font face=Courier New><SPAN style="color:#007F00">'------------------------------------------------------------------------------</SPAN>
<SPAN style="color:#00007F">Function</SPAN> ConvertNumToCol(intNum <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> intTens <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, intOnes <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> strTens <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strOnes <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    
    intTens = Int((intNum - 1) / 26)
    <SPAN style="color:#00007F">If</SPAN> intTens > 0 <SPAN style="color:#00007F">Then</SPAN> strTens = Chr(64 + intTens) <SPAN style="color:#00007F">Else</SPAN> strTens = ""
    
    intOnes = intNum Mod 26
    <SPAN style="color:#00007F">If</SPAN> intOnes = 0 <SPAN style="color:#00007F">Then</SPAN> intOnes = 26
    strOnes = Chr(64 + intOnes)
    ConvertNumToCol = strTens & strOnes
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>
<SPAN style="color:#007F00">'------------------------------------------------------------------------------</SPAN></FONT>
 
Upvote 0
Fletchmeister said:
I'm easy, which ever way is the easiest. I have a pivot table in place so if I can have the code to either update this once the data has changed or the code to create a new one!

I'm finding the pivot table setup on 2000 very frustrating!!

Fletch :rolleyes:
Click to expand...

What is the current range of the source data? Does the data expand (or shrink) vertically or horizontally?
 
Upvote 0
Fletch,

I'm not sure how the code I posted will respond to expansion to new columns (especially if new columns appear to the left of columns that get used in the PT). You might try it and see if it works for you.
 
Upvote 0
Fletchmeister said:
It will expand and shrink in both directions unfortunately, the data is very volatile!

Fletch :oops:
Click to expand...

OK. What is the current range? As Greg notes, new columns/fields require manual post-processing.
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Change Data source of Pivot Table from VBA
Replies
4
Views
202
ItalianPlatinum
I
U
  • Solved
Re-organizing pivot table fields in vba
Replies
2
Views
585
ummjay
U
S
  • Solved
VBA to create pivot tables
2
Replies
12
Views
1K
Domenic
D
B
  • Solved
Recording macro pivot table error
Replies
2
Views
994
biglb79
B
T
  • Question
VBA Dynamic PivotTable Name
Replies
4
Views
490
th259
T

Forum statistics

Threads
1,214,877
Messages
6,122,051
Members
449,064
Latest member
scottdog129

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top