ok so i got it so it doesnt error out when i enter it in..

but when i run the macro it crashes.. can someone check this for me
please.
Sub getunitqty(mybook As Workbook)

Dim i As Integer
i = 20
Do
mybook.Worksheets("Product Breakout").Range("E" & i).Value = _
"=GETPIVOTDATA(""YTD Total Units"",[PERSONAL.XLSB]unitqty!
R3C1,""SIEBELLINKID,""personal.xlsb!NumberIt(''Rev enue & Product
Data''!A1"",CU_CODE,""range(""A""&i).value)"


i = i + 1
Loop While i < 61

end sub

On Apr 19, 4:14 pm, mithu wrote:
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('Reve nue & Product Data'!
A1,""CU_CODE," & Range("A" & i).Value)""

On Apr 19, 3:22 pm, mithu 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- Hide quoted text -

- Show quoted text -

I am far from expert on the worksheet formulas but it would seem that the
comma separaters should consistently be either inside or outside the double
quotes, and they are not in the formula that you posted.

"mithu" wrote:

ok so i got it so it doesnt error out when i enter it in..

but when i run the macro it crashes.. can someone check this for me
please.
Sub getunitqty(mybook As Workbook)

Dim i As Integer
i = 20
Do
mybook.Worksheets("Product Breakout").Range("E" & i).Value = _
"=GETPIVOTDATA(""YTD Total Units"",[PERSONAL.XLSB]unitqty!
R3C1,""SIEBELLINKID,""personal.xlsb!NumberIt(''Rev enue & Product
Data''!A1"",CU_CODE,""range(""A""&i).value)"


i = i + 1
Loop While i < 61

end sub

On Apr 19, 4:14 pm, mithu wrote:
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('Reve nue & Product Data'!
A1,""CU_CODE," & Range("A" & i).Value)""

On Apr 19, 3:22 pm, mithu 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- Hide quoted text -

- Show quoted text -

Also, should that not be entered as a formula instead of a value?

"mithu" wrote:

ok so i got it so it doesnt error out when i enter it in..

but when i run the macro it crashes.. can someone check this for me
please.
Sub getunitqty(mybook As Workbook)

Dim i As Integer
i = 20
Do
mybook.Worksheets("Product Breakout").Range("E" & i).Value = _
"=GETPIVOTDATA(""YTD Total Units"",[PERSONAL.XLSB]unitqty!
R3C1,""SIEBELLINKID,""personal.xlsb!NumberIt(''Rev enue & Product
Data''!A1"",CU_CODE,""range(""A""&i).value)"


i = i + 1
Loop While i < 61

end sub

On Apr 19, 4:14 pm, mithu wrote:
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('Reve nue & Product Data'!
A1,""CU_CODE," & Range("A" & i).Value)""

On Apr 19, 3:22 pm, mithu 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- Hide quoted text -

- Show quoted text -

if i enter it in as a formula do i still have to include the quotes?..
and how would i include the variable i so it sees it as a variable in
the code not a character in the formula?

does anyone have an example of a formula being minuplated by a macro?

thank you so much for helping me with this guys.

On Apr 19, 8:34 pm, JLGWhiz wrote:
Also, should that not be entered as a formula instead of a value?



"mithu" wrote:
ok so i got it so it doesnt error out when i enter it in..

but when i run the macro it crashes.. can someone check this for me
please.
Sub getunitqty(mybook As Workbook)

Dim i As Integer
i = 20
Do
mybook.Worksheets("Product Breakout").Range("E" & i).Value = _
"=GETPIVOTDATA(""YTD Total Units"",[PERSONAL.XLSB]unitqty!
R3C1,""SIEBELLINKID,""personal.xlsb!NumberIt(''Rev enue & Product
Data''!A1"",CU_CODE,""range(""A""&i).value)"

i = i + 1
Loop While i < 61

end sub

On Apr 19, 4:14 wrote:
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('Reve nue & Product Data'!
A1,""CU_CODE," & Range("A" & i).Value)""

On Apr 19, 3:22 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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -