Create a Pivottable in a macro
 

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?

Create a Pivottable in a macro
 

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

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Michael" wrote:

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?

Create a Pivottable in a macro
 

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

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, 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.

"Dave F" wrote:

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

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Michael" wrote:

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?

Create a Pivottable in a macro
 

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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Michael" wrote:

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

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, 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.

"Dave F" wrote:

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

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Michael" wrote:

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?

Create a Pivottable in a macro
 

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

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, 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 F" wrote:

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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Michael" wrote:

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

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, 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.

"Dave F" wrote:

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

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Michael" wrote:

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?

Create a Pivottable in a macro
 

Maybe (untested):

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

Michael wrote:

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

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, 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 F" wrote:

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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Michael" wrote:

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

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, 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.

"Dave F" wrote:

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

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Michael" wrote:

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?

--

Dave Peterson

Create a Pivottable in a macro
 

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))

--
Regards

Roger Govier


"Michael" wrote in message
...
I understand that the code for my pivot in the macro is going to be

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, 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 F" wrote:

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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Michael" wrote:

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

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
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.

"Dave F" wrote:

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

Dave
--
A hint to posters: Specific, detailed questions are more likely
to be
answered than questions that provide no detail about your
problem.


"Michael" wrote:

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?

Create a Pivottable in a macro
 

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" wrote:

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))

--
Regards

Roger Govier


"Michael" wrote in message
...
I understand that the code for my pivot in the macro is going to be

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, 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 F" wrote:

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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Michael" wrote:

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

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
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.

"Dave F" wrote:

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

Dave
--
A hint to posters: Specific, detailed questions are more likely
to be
answered than questions that provide no detail about your
problem.


"Michael" wrote:

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?

Create a Pivottable in a macro
 

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.

--
Regards

Roger Govier


"Michael" wrote in message
...
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" wrote:

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))

--
Regards

Roger Govier


"Michael" wrote in message
...
I understand that the code for my pivot in the macro is going to be

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, 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 F" wrote:

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
--
A hint to posters: Specific, detailed questions are more likely to
be
answered than questions that provide no detail about your problem.


"Michael" wrote:

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

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
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.

"Dave F" wrote:

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

Dave
--
A hint to posters: Specific, detailed questions are more
likely
to be
answered than questions that provide no detail about your
problem.


"Michael" wrote:

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?