Dynamic Range and Pivot problem

[Immatoity]

hi,

I have set a dynamic range called Bexley Pivot Data on a sheet called "Bexley Copy Data", which has data in cols A:H, by using the procedure, Insert, name, define

and the following formula =OFFSET(Bexley Copy Data!A$1,0,0,COUNTA(Bexley Copy Data!$A:$A),8)

I have a pivot table on a seperate sheet called Pivot, and I have tried to make the source data the range "Bexley Pivot Data", and it says "Reference not valid"...driving me mad...

also, I have noticed that I cannot select the range in the dropdown section...its just not there....

I have some VBA which is supposed to refresh this pivot, but obviously it can't! It seems to pick up range A14999 every time, god knows why

[royUK]

It looks to me like your Names are not valid. Try removing the spaces

[Immatoity]

hi roy

cheers for that but I mis-typed in my post...the rangename I am trying to use is BexleyPivotData...has no spaces...

The sheet name does ie Bexley Copy Data...don't think thats it?

[Bob Phillips]

hi roy

cheers for that but I mis-typed in my post...the rangename I am trying to use is BexleyPivotData...has no spaces...

The sheet name does ie Bexley Copy Data...don't think thats it?

No but you should enclose with single quotes ws names with spaces.

But that won't solve your problem.

[Immatoity]

hmmm a little confused by your reply xld??

What should I do?

[Bob Phillips]

hmmm a little confused by your reply xld??

What should I do?

Sorry mate, didn't mean to confuse.

I am afraid I don't know, I dislike pivot tables with a passion, so I never use them. I did a test with your data, making appropriate corrections, but it still didn't work, so I came to the (maybe incorrect) conclusion that pivot tables do not support dynamic ranges.

If I am correct, you are stuffed. But, I like to think Excel is more flexible that that, so maybe someone will prove me wrong.

[royUK]

When I use PivotTables - which I personally find them to be invaluable, I use Dynamic Named ranges. Reading your post properly, it seems to me that you are referencing a non-existent range. Recreate the name & check the name exists afterwards.If you are having trouble with the range attach your workbook.

[royUK]

here's an example using a Dynamic named range

[Immatoity]

cheers lads...will try that...but will have to be at home later, as works firewall does not allow zip files to be downloaded...even if they will save users within the business hours!!

ridiculous!

I will also upload my file if I can't get it working...

ta for now

[Immatoity]

Hi....sorry forgot to dload at home....can someone kindly email me the file? Zip files are allowed via email...pm me for address plz

[SherryO]

Hi, I was searching for something else and came across this. Did you ever get it sovled? You could try the following for the named range.
(=OFFSET(Source!$A$1,0,0,COUNTA(Source!$A:$A),COUNTA(Source!$1:$1)))
Where Source would be your BexleyPivotData.
I hope it help, albiet very late.
SherryO

[XLGibbs]

The named range itself is not the issue, and they won;t always show up in the drop down menu. BASIC syntax for refresh tables in code...

[VBA]
Sub RefreshPivot()
Dim Ws As Worksheet
Dim PT As PivotTable
Set Ws = Sheets("Sheet1") 'name your sheet containing the pivot table

Ws.Activate
ActiveSheet.PivotTables("PivotTable1").RefreshTable
'in Pivot Table >Table Options you can see the name
'change the name to your pivot table


'Note: You do not refresh the named range, only the query table..

End Sub
[/VBA]

[SherryO]

Replied to the wrong thread... Oops

[XLGibbs]

Sherry, my reply was intended for the orginal poster, I have replied to your new thread...