PivotTable range [VBA]

[Martin_H]

Hi,

I'm trying to figure out, how to refer to Pivot Table Range without having to directly type "$A$1:$BA$776" because the range varies. I would like to make it indirectly.

Here is part of the code.

Dim ws As Worksheet
Dim pc As PivotCache, pt As PivotTable
Dim ds As Range

Set ds = ActiveWorkbook.Worksheets("JUNE_DATA").Range("$A$1:$BA$776")
Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, ds)

Help is much appreciated.


Thank you.

 

[Saba Sabaratnam]

Maybe

Set ds = ActiveWorkbook.Worksheets("JUNE_DATA").Range("$A$1").CurrentRegion

Kind regards

Saba

 

[Martin_H]

Hi Saba, thank you for your response.

Unfortunately it does not work.

 

[Alex Blakenburg]

What happened when you tried @Saba Sabaratnam code. It should work unless,
your data doesn't start in A1 or you have column OR rows that are completely blank between your top left corner and bottom right corner of the data set.

Do you know how to set a breakpoint and how to use the immediate window ?

 

[Martin_H]

What happened when you tried @Saba Sabaratnam code. It should work unless,
your data doesn't start in A1 or you have column OR rows that are completely blank between your top left corner and bottom right corner of the data set.

Do you know how to set a breakpoint and how to use the immediate window ?

Run-time error '9':
Subscript out of range

I have a sheet with big data table (Formated as Table) where each header has a value but there are cells inside the table that are blank.

 

[Alex Blakenburg]

One of the big benefits of Tables is that they auto-expand, so all you have to do is use the table name:

I will send you the code in a few minutes.

 

[Alex Blakenburg]

The simplest code is this:
Just put your table name inside the quotes but leave the [#ALL] on the end inside the quotes.

Set ds = Range("ENTER_YOUR_TABLENAME[#ALL]")

 

[Martin_H]

The simplest code is this:
Just put your table name inside the quotes but leave the [#ALL] on the end inside the quotes.

Set ds = Range("ENTER_YOUR_TABLENAME[#ALL]")

Works!

Thank you very much Alex.

 

[Alex Blakenburg]

Thanks for letting us know, glad we could help.

 

[Martin_H]

The simplest code is this:
Just put your table name inside the quotes but leave the [#ALL] on the end inside the quotes.

Set ds = Range("ENTER_YOUR_TABLENAME[#ALL]")

Just a question.

Is it also possible to refer to Pivot Table this way?