Run-time error '5':Invalid Procedure call or argument

[Jan Refsdal]

I'm attempting to write VBA code for a pivot table range selection, this
expression works when SourceData is set to a fixed range, but when set to
the varaible range selection as noted below I receive the subject error
message

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Sheets("Sheet1").Range(Cells(1, 1), Cells(i,5))).CreatePivotTable _
TableDestination:="", _
TableName:="PivotTable9"

Thanks

 

[Dave Peterson]

What version of excel are you using?

Your code worked ok for me (if I was careful). If I was less than careful, I
got 1004 errors.

One of the things to be careful about is this portion:
Sheets("Sheet1").Range(Cells(1, 1), Cells(i,5))

Cells(1,1) and cells(i,5) are unqualified. That means they point at the
activesheet (if this is in a general module). And the activesheet may not
always be sheet1 when you run your code.

I _thought_ that xl97 was more stringent with sourcedata. IIRC, it liked a
string--not a range. xl2k and xl2002 accept either string (like the address) or
the range.

(And you're using pivotcaches.add and that was added in xl2k. So that shouldn't
be the cause!)

Anyway, this might work for you.

Dim i As Long
Dim myRng As Range

i = 33 'however you get it
With Worksheets("sheet1")
Set myRng = .Range(.Cells(1, 1), .Cells(i, 5))
End With

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
myRng.Address(external:=True)).CreatePivotTable _
TableDestination:="", TableName:="PivotTable9"