Hi

you could insert some code to find the lastrow

Dim lastrow as long
Lastrow = Cells(Rows.Count, 1).End(xlUp).row

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Y:\\[Report.XLS]Sheet1'!Range(Cells(2,1),cells(lastrow,5)) _
).CreatePivotTable TableDestination:=Range("A4"),
TableName:="PivotTable1"

--
Regards

Roger Govier


"houghi" wrote in message
...
Hello,

If this is not the correct group, please let me know which one is.

I am pretty new to Excel scripting. I am able to do what I need most
of
the time.

My problem is that I want to place a pivot table in a scriptand that
works, but the
data range is never the same. I have recorded a macro an that works
for said data. It gives me the following:


ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"'Y:\\[Report.XLS]Sheet1'!R2C1:R37C5" _
).CreatePivotTable TableDestination:=Range("A4"),
TableName:="PivotTable1"

However the range can go from R2C1:R3C5 to R2C1:R7000C5 and will be
different almost each time.

I have tried to take a range that is way too big, but then the Pivot
table gives me the wrong results.
So what is the best way to select the correct range each time?

I have searched with Google for a few days, but came up with nothing.
:-(

I am working with Excel 2000. Sorry, no option in updating or
upgrading.
Company policy.

houghi
--
Knock-knock.
Who's there?
Under the Patriot Act, we don't have to tell you that.