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?