Create a Pivottable in a macro

[Guest]

Hi, when I run a macro I would like to create a pivottable, however, the
data range changes each time I run the macro.

So the pivottable that I recorded only selects the original range

eg when the macro was recorded the range of data was from say A1 to Z1000
but next time I run the macro the data range could be A1 to Z2000.
Because the range ended at Z1000 when the macro was recorded, the pivot only
goes down to Z1000

I presume I need to somehow name a range and use that when calling the
pivottable but I'm not that advanced, can anyone help please?

 

[Guest]

Have a look at dynamic named ranges:
http://www.ozgrid.com/Excel/DynamicRanges.htm

Dave

 

[Guest]

thanks, but how do I put that into a macro.
currently the code in the macro is like this:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R3319C20").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable9", DefaultVersion:=xlPivotTableVersion10

I realise that Sheet1!R1C1:R3319C20 is what needs to be changed depending on
what the current range is but don't know the code to do it?
Any help is much appreciated.

 

[Guest]

You need to create the dynamic named range following the instructions at the
link I provide. Then, in place of the range you specify in your macro, place
the dynamic named range.

Dave

 

[Guest]

I understand that the code for my pivot in the macro is going to be

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=
Myrange).CreatePivotTable TableDestination:="",
TableName:= "PivotTable9", DefaultVersion:=xlPivotTableVersion10

However, I don't know what the code would be to name the range in the first
place. Because the file that the macro is run on is downloaded from another
program, there are no ranges named and I want the macro to do that.

 

[Dave Peterson]

Maybe (untested):

....SourceData:=worksheets("sheetnamehere").range("MyRange")).createpivottable...

 

[Roger Govier]

Hi Michael

to define your range

Dim Myrange as Range, lastrow as long, lastcol as long
lastrow = Cells(Rows.Count, 1).End(xlUp).row
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Myrange = Range(Cells(1,1), Cells(lastrow, Lastcol))

If you know that it is always going to be column 20, then you need only
calculate the lastrow and use
Myrange = Range(Cells(1,1), Cells(lastrow, 20))

 

[Guest]

Thank you everyone for your response.

I also found reference to a formula that looks simpler and works a treat:

Selection.CurrentRegion.Name = "myrange"

 

[Roger Govier]

Hi Michael

Selection.CurrentRegion.Name = "myrange"

This may be working fine for you at the moment, but Current Region can
give inaccurate results if there are any row deletions of records within
the overall range.
Just something you need to be aware of.