Pivot and VB scripting range problem

[houghi]

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:=xlDatabase, 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
--

 

[Roger Govier]

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:=xlDatabase, SourceData:= _
"'Y:\\[Report.XLS]Sheet1'!Range(Cells(2,1),cells(lastrow,5)) _
).CreatePivotTable TableDestination:=Range("A4"),
TableName:="PivotTable1"

--
Regards

Roger Govier

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:=xlDatabase, 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]

I get a 'Runtime error 1004'. Reference is not valid. With 'debug' the
whole line is yellow. When I change !Range(Cells(2,1),cells(lastrow,5))
back to !R2C1:R37C5 then it works again.

Could it be that this not work because I work with Excel 2000? If that
is the case, is there a different solution?

 

[Roger Govier]

Hi

What value shows for lastrow?

--
Regards

Roger Govier

I get a 'Runtime error 1004'. Reference is not valid. With 'debug' the
whole line is yellow. When I change
!Range(Cells(2,1),cells(lastrow,5))
back to !R2C1:R37C5 then it works again.

Could it be that this not work because I work with Excel 2000? If that
is the case, is there a different solution?

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:=xlDatabase, SourceData:=
_
"'Y:\\[Report.XLS]Sheet1'!Range(Cells(2,1),cells(lastrow,5))
_
).CreatePivotTable TableDestination:=Range("A4"),
TableName:="PivotTable1"


houghi

 

[houghi]

I have added the following
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox lastrow

That shows 37. To be sure I even did ` lastrow = "37" ` so there won't
be an issue with calculation lastrow.

 

[Dave Peterson]

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.

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:=xlDatabase, 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]

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.

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.

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:=xlDatabase, 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

 

[Dave Peterson]

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

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.

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.

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:=xlDatabase, 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

houghi

 

[houghi]

I had a couple of typos (sorry).

This compiles, but I didn't test it:

Option Explicit

<snip>

This works. Great, thanks.

houghi