Pivot Table - Named Range

[Carly]

I have got a spreadsheet which contains 53,000 lines and 87 columns and I want to pivot the whole thing. I have named the data range, 'Data' but when I try & pivot it using this named range is says:

"Data source reference is not valid"

If I then change the range to be 'New Business'!$A$1:$CI$52925 it works.

Is there any way I can stop this from happening as the size of this data will always change?

 

[Jameo]

Hi, when defining the data for your pivot table, just use the offset formula you used to define your named range instead.

i.e. =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))


Now need for the range to be named, just need to use offset.

 

[Carly]

Thanks for the quick reply.

Would it be possible to explain how I would change this piece of VBA code to use the formula you suggested as when I record it, it just puts the cell references in:

    ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "\\acer108\bw\NewBusinessReport\Templates\[NewBusinessReport (Raw Data) - TEMPLATE.xlsx]New Business!R1C1:R52925C87" _
        , Version:=xlPivotTableVersion12)

 

[RoryA]

The idea is to use the formula as the definition of your named range so that it automatically expands as you add data, then all you need to do is refresh the table. See Debra's tip here for example.

 

[Carly]

Thanks for the help on this...I have now change the code to the following:

ActiveWorkbook.Names.Add Name:="Data", RefersToR1C1:="=OFFSET('New Business'!R1C1,0,0,COUNTA('New Business'!C1),COUNTA('New Business'!R1))"
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Data", Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:="Pivot!R1C1", TableName:="PivotTable1" _
        , DefaultVersion:=xlPivotTableVersion12

and it works...

I don't understand why my original code didn't work as it always has, but I'm happy to have a solution.

 

[Jameo]

You can use a named range as well, it should have worked for you.

i.e.

 ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'someworkbook'!myrange"). _
        CreatePivotTable TableDestination:="[Book1]Sheet2!R3C1", TableName:= _
        "PivotTable2", DefaultVersion:=xlPivotTableVersion1

 

[Carly]

This is what I normally do & have got many reports which use this it in this way already, which I've written over the past 10 years, but for some reason on this large amount of data, it doesnt like it, manually or in VBA if I just name the range & use that.

Not sure why, it doesn't seem to make any sense to me.

 

[Carly]

My mistake, something in someone else's code that I was using was creating a named range with the same name! Thank you all so much for your help.