hello.. i am writing code that allows a worksheet to access data from
a pivot table in my personal.xlsb workbook. i pass in a workbook to
this function.

the cells i am trying to enter this quoted getpivotdata formuls start
in e20 and go to e60.

i made a variable called i to reperesent the row number

this is my code

Dim i As Integer
i = 20
Do

mybook.Worksheets("Product Breakout").Range("E"&i).Value =
"=GETPIVOTDATA(" & "YTD Total Units" & ",[PERSONAL.XLSB]unitqty!$A$3,"
& "SIEBELLINKID" & ",personal.xlsb!NumberIt('Revenue & Product Data'!
A1)," & "CU_CODE" & ",range(A" & i&").value)"

i = i + 1
Loop While i < 61

the part of the code that says range(A"&i&").value pulls the value of
column a for which ever row the counter is in. can someone help me
fix this please

ok so i did a record macro and i got this far.. i am still getting an
error but the code looks cleaner
mybook.Worksheets("Product Breakout").Range("A" & i).Value = _
"=GETPIVOTDATA(""YTD Total Units"",[PERSONAL.XLSB]unitqty!
R3C1,""SIEBELLINKID"",personal.xlsb!NumberIt('Revenue & Product Data'!
A1,""CU_CODE," & Range("A" & i).Value)""


On Apr 19, 3:22 pm, mithu <mith...@gmail.com> wrote:
> hello.. i am writing code that allows a worksheet to access data from
> a pivot table in my personal.xlsb workbook. i pass in a workbook to
> this function.
>
> the cells i am trying to enter this quoted getpivotdata formuls start
> in e20 and go to e60.
>
> i made a variable called i to reperesent the row number
>
> this is my code
>
> Dim i As Integer
> i = 20
> Do
>
> mybook.Worksheets("Product Breakout").Range("E"&i).Value =
> "=GETPIVOTDATA(" & "YTD Total Units" & ",[PERSONAL.XLSB]unitqty!$A$3,"
> & "SIEBELLINKID" & ",personal.xlsb!NumberIt('Revenue & Product Data'!
> A1)," & "CU_CODE" & ",range(A" & i&").value)"
>
> i = i + 1
> Loop While i < 61
>
> the part of the code that says range(A"&i&").value pulls the value of
> column a for which ever row the counter is in. can someone help me
> fix this please

Mithu,
is the problem with

""CU_CODE," & Range("A" & i).Value)""

since you have double quotes at either end maybe Excel is taking all
the characters in the middle as a single string and therefore not
evaluating "A" & i as A1 , A2 etc

Have you tried ....

""CU_CODE," & Range("A" "" & i & "" ).Value)"" or something like
that.

Catherine