Hi,
I'm making a pivot table from data imported to a
spreadsheet. It works just fine in XP, however it will
not perform in Excel 2000. Code:

Dim PivotRange as Range
..
Set PivotRange = Worksheets(1).UsedRange
..
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase _
SourceData:=PivotRange).CreatePivotTable _
TableDestination:="", TableName:="MvsC"

When reaching the line where the table is created Excel
just quits the current sub and returns to the calling. If
I change the SourceData from the PivotRange variable to
an absolute address like "'Sheet1'!R1C1:R12C20" it works
fine, but that is quite an inconvenient way.
Anybody know why this happens to me?
Any help appreciated.

ojv

In Excel 2000, SourceData requires a string. The following should work
in both versions:
'===================================
Dim PivotRange As Range
Dim strPR As String
'
Set PivotRange = Worksheets(1).UsedRange
strPR = PivotRange.Address
'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:=strPR).CreatePivotTable _
TableDestination:="", TableName:="MvsC"
'======================================

jacob wrote:
Hi,
I'm making a pivot table from data imported to a
spreadsheet. It works just fine in XP, however it will
not perform in Excel 2000. Code:

Dim PivotRange as Range
.
Set PivotRange = Worksheets(1).UsedRange
.
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase _
SourceData:=PivotRange).CreatePivotTable _
TableDestination:="", TableName:="MvsC"

When reaching the line where the table is created Excel
just quits the current sub and returns to the calling. If
I change the SourceData from the PivotRange variable to
an absolute address like "'Sheet1'!R1C1:R12C20" it works
fine, but that is quite an inconvenient way.
Anybody know why this happens to me?
Any help appreciated.

ojv


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Of course!. Thank you for taking the time.
-----Original Message-----
In Excel 2000, SourceData requires a string. The
following should work
in both versions:
'===================================
Dim PivotRange As Range
Dim strPR As String
'
Set PivotRange = Worksheets(1).UsedRange
strPR = PivotRange.Address
'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlData base, _
SourceData:=strPR).CreatePivotTable _
TableDestination:="", TableName:="MvsC"
'======================================

jacob wrote:
Hi,
I'm making a pivot table from data imported to a
spreadsheet. It works just fine in XP, however it will
not perform in Excel 2000. Code:

Dim PivotRange as Range
.
Set PivotRange = Worksheets(1).UsedRange
.
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase _
SourceData:=PivotRange).CreatePivotTable _
TableDestination:="", TableName:="MvsC"

When reaching the line where the table is created
Excel
just quits the current sub and returns to the calling.
If
I change the SourceData from the PivotRange variable
to
an absolute address like "'Sheet1'!R1C1:R12C20" it
works
fine, but that is quite an inconvenient way.
Anybody know why this happens to me?
Any help appreciated.

ojv


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.