please help with get pivotdata formula
 

i am having trouble with this code. I am trying to pull data from a
pivot table i created in my personal.xlsb workbook..

Variable "sid" pulls the company code from another workbook. this
works perfectly. but the error happens when i enter the do while
loop, when i want to place the getpivotdata formula in the cell.
can someone please help me with this. I think the problem is
happening due to my quotes.. because i am trying to pull values from
variables in my code and insert them into the string. i cant seem to
figure out if my quotes are just in the wrong place.. or if i am just
not doing it correctly. .

If anyone would be kind enough to help me Iwould greatly appriciate
it.
here is my code

Dim sid As Double
Dim i As Integer

sid = Application.Run("'personal.xlsb'!Numberit",
mybook.Worksheets("Revenue & Product Data").Range("A1"))
i = 20

Do While i < 61
Range("E" & i).Select
ActiveCell.FormulaR1C1 = _
"=GETPIVOTDATA(""YTD Total Units"",[PERSONAL.XLSB]Sheet5!
R3C1,""SIEBELLINKID""," & CStr(sid) & ",""CU_CODE"",$A" & CStr(i) &
")"
i = i + 1

Loop

please help with get pivotdata formula
 

If I replace you variables with string constants ("---") I get

? "=GETPIVOTDATA(""YTD Total
Units"",[PERSONAL.XLSB]Sheet5!R3C1,""SIEBELLINKID""," & "---" &
",""CU_CODE"",$A" & "---" & ")"
=GETPIVOTDATA("YTD Total
Units",[PERSONAL.XLSB]Sheet5!R3C1,"SIEBELLINKID",---,"CU_CODE",$A---)

from the immediate window. Is that what you want (the last part of the
formula looks suspect - but it is difficult to know what you are trying to
achieve).

Anyway, the quotes don't appear to be the problem in most of your formula.

--
Regards,
Tom Ogilvy

"mithu" wrote:

i am having trouble with this code. I am trying to pull data from a
pivot table i created in my personal.xlsb workbook..

Variable "sid" pulls the company code from another workbook. this
works perfectly. but the error happens when i enter the do while
loop, when i want to place the getpivotdata formula in the cell.
can someone please help me with this. I think the problem is
happening due to my quotes.. because i am trying to pull values from
variables in my code and insert them into the string. i cant seem to
figure out if my quotes are just in the wrong place.. or if i am just
not doing it correctly. .

If anyone would be kind enough to help me Iwould greatly appriciate
it.
here is my code

Dim sid As Double
Dim i As Integer

sid = Application.Run("'personal.xlsb'!Numberit",
mybook.Worksheets("Revenue & Product Data").Range("A1"))
i = 20

Do While i < 61
Range("E" & i).Select
ActiveCell.FormulaR1C1 = _
"=GETPIVOTDATA(""YTD Total Units"",[PERSONAL.XLSB]Sheet5!
R3C1,""SIEBELLINKID""," & CStr(sid) & ",""CU_CODE"",$A" & CStr(i) &
")"
i = i + 1

Loop