Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error '5':Invalid Procedure call or argument
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:=xlDatab ase, SourceData:= _ Sheets("Sheet1").Range(Cells(1, 1), Cells(i,5))).CreatePivotTable _ TableDestination:="", _ TableName:="PivotTable9" Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error '5':Invalid Procedure call or argument
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:=xlDatab ase, SourceData:= _ myRng.Address(external:=True)).CreatePivotTable _ TableDestination:="", TableName:="PivotTable9" Jan Refsdal wrote: 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:=xlDatab ase, SourceData:= _ Sheets("Sheet1").Range(Cells(1, 1), Cells(i,5))).CreatePivotTable _ TableDestination:="", _ TableName:="PivotTable9" Thanks -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
call procedure problem | Excel Discussion (Misc queries) | |||
Run-time error '5': Invalid procdre call or argumnt - End/Debug/He | Excel Discussion (Misc queries) | |||
Invalid outside procedure (creating a PDF button as a macro) | Excel Worksheet Functions | |||
Run-time Error '5' - Invalid procedure call or argument | Excel Discussion (Misc queries) | |||
Run-time error '5': Invalid Procedure Call or Argument | Excel Discussion (Misc queries) |