add code to a cell via macro

M

mithu

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 personal.xlsb!NumberIt('Revenue & Product Data'! A1) code just
extracts the company id number from cell a1 of the 'revenue & Product
data' sheet.

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
 
M

mithu

ok i got the code to look a little cleaner.. it is still not working
but it is a lot 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)""
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top