I had a couple of typos (sorry).

This compiles, but I didn't test it:

Option Explicit
Sub testme()

Dim RptWkbk As Workbook
Dim ActWks As Worksheet
Dim LastRow As Long
Dim PTRng As Range

Set ActWks = ActiveSheet '<--changed

Set RptWkbk = Workbooks.Open(Filename:="y:\Report.xls")
With RptWkbk.Worksheets(1)
'I used column A to find the last used row
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set PTRng = .Range("a2:E" & LastRow)
End With

'do more stuff???
With ActWks
.Parent.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=PTRng.Address(external:=True)) _
.CreatePivotTable TableDestination:=.Range("A4"), _
TableName:="PivotTable1"
End With '<--added

'do more stuff
RptWkbk.Close savechanges:=False

End Sub



houghi wrote:

Thanks, I get the basic idea, but unfortunatly I am still in the copy
and paste fase of learning the language, so the compile error I get is a
stopper for me. I do not know how to solve that.
I have added sub Pivot_2() at the beginning and End Sub at the end.

The rest will be for monday, as I have no access to Excel over the
weekend.

Dave Peterson wrote:
Can you open that report.xls file to find the last used row?

dim RptWkbk as workbook
dim ActWks as worksheet
dim LastRow as long
dim PTRng as range

set actwkbk = activesheet

set rptwkbk = workbooks.open(Filename:="y:\Report.xls")
with rptwkbk.worksheets(1)
'I used column A to find the last used row
lastrow = .cells(.rows.count,"A").end(xlup).row
set ptrng = .range("a2:E" & lastrow)
end with

'do more stuff???
with actwks
.parent.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=ptrng.address(external:=true)) _
.CreatePivotTable TableDestination:=.Range("A4"), _
TableName:="PivotTable1"

'do more stuff
rptwkbk.close savechanges:=false

====
Uncompiled, untested. Watch for typos.


houghi wrote:

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.


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

--

Dave Peterson