Avatar of PeterBaileyUk
PeterBaileyUk
 asked on

dynamic data range for excel pivot table and excel graph

I have a sheet that has raw data and then a pivot table based on that data and then a graph based on the pivot table.

How can I in vba in excel expand the range of the pre existing pivot table and graph automatically.

I was using this to clear down the data on open and prior to appending the data so I think its the reverse of that ie get used range and use that value for the pivot and graph

With Sheets("CW data")
    Intersect(.UsedRange, .Range("A:N")).ClearContents
  End With

help would be appreciated hope i explained it ok.

The width of columns remains constant but not the data rows.
Microsoft Excel

Avatar of undefined
Last Comment
PeterBaileyUk

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Rory Archibald

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Rob Brockett

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Rob Brockett

Ooopps, I should have refreshed my page before posting. At least you are getting consistent suggestions & I'm thinking along the same lines as Rory (if you ignore the verbosity of my example!).

Actually, embarrassingly, I've just realised that if column A is deleted, a lot of the Names in my example cause #ref! errors, so my example isn't just verbose... it is not even effective in its goal of "delete happey users" :-(

Rob
byundt

If you want to change the range in VBA, here is some sample code that does so in Excel 2010:

Sub NewPTrange()
Dim pc As PivotCache
Dim pt As PivotTable
Dim rg As Range
Dim v As Variant
Dim ws As Worksheet
Set pt = ActiveSheet.PivotTables(1)
Set pc = pt.PivotCache
v = Split(pc.SourceData, "!")
Set ws = Worksheets(v(0))
Set rg = ws.Range(Application.ConvertFormula(v(1), xlR1C1, xlA1))
Set rg = rg.Cells(1, 1).CurrentRegion
pc.SourceData = v(0) & "!" & rg.Address(True, True, xlR1C1)
End Sub

Open in new window

SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PeterBaileyUk

ASKER
I have followed the above links but am using excel 2010 and cannot see how to name the range dynamically as the menu choices they give are not the same and i cannot find them in 2010
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Rory Archibald

Formulas tab, Name Manager, click 'New...' then set up the name as directed. :)
PeterBaileyUk

ASKER
I see  matthewpatrick suggests tables for 2010 not sure where it shows how to use those, the data is imported from an access query.
PeterBaileyUk

ASKER
I found the tables part just experimenting now.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PeterBaileyUk

ASKER
I have added some code that turns the rows into a table on the worksheet courtesy of an EE expert. I am not sure how to amend the existing pivot table to use the table I just created as the link you showed shows what to do if creating the pivot table from new.
PeterBaileyUk

ASKER
ok I created the pivot table using the table 1 as the data source so is that enough now, I assume as its a table the table will dynamically expand and contract automatically and as the pivot table is linked to the table the process now occurs.

on opening the spredsheet the db clears out the raw data and repopulates and then creates the tables will that be a problem for the existing pivot table?
Rob Brockett

It shouldn't cause a problem as long as the table is re-populated before the pivot-table gets refreshed. If the pivot-table is refreshed when there is no data you may get an error along the lines of "a pivot table must have at least two rows as its source data".

Rob
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
PeterBaileyUk

ASKER
Ok i wasnt sure as it mentioned refreshing of ptcache in the instructions provided above.