Defining a workbook, worksheet, with R1C1

[TeloS53]

hey guys !

So, i have 2 questions, i am trying to paste the getpivotdata formula in a cell, but the formula is coming from another workbook, so i would like to know if there is a way to define them, to adapt them in the formula. I have put in bold what i would like you to automatize...
Second question, i would like to know if it is possible to automatically change the number at the end of the formula, to adapt to each code in a range of cells, that i would look up for first, and then doing a loop until the end

By advance, thanks

Sub Button2_Click()
Dim cel As Range
Dim wbk1, wbk2 As Workbook
Dim sht1, sht2 As Worksheet
Set wbk1 = Application.ThisWorkbook
Set sht1 = wbk1.Sheets("MPC Business Figures")

MsgBox "Please, choose the source file which will be used in order to update data. It should be located in (G:) Department Share/ IT Services Europe/ Central Info/ MPC Business figures (Impact)"
    Sourcefile = Application.GetOpenFilename
    If Sourcefile <> False Then
       Workbooks.Open Filename:=Sourcefile
    Else
        MsgBox "No file has been selected"
        Exit Sub
    End If
Sheet = InputBox("Please type now the exact name of the sheet in which the information you want to paste here are coming from.")
Set wbk2 = ActiveWorkbook
Set sht2 = wbk2.Sheets(Sheet)
wbk1.Activate
sht1.Activate
Set cel = Cells.Find(What:="Test", LookIn:=xlValues, _
                    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)


Cells(13, cel.Column).FormulaR1C1 = "=GETPIVOTDATA(""[Measures].[EuroValue]"", '[wbk2]sht2'!R6C1,""[SalesLevel].[SalesLevelHierarchy]"",""[SalesLevel].[SalesLevelHierarchy].&[1]"",""[SetProducts]"",""[Product].[ProductHierarchy].&[2]"",""[SetAccounts]"",""[Account].[AccountId].&[26]"",""[SetRegions]"",""[Region].[RegionHierarchy].&[9989]"")"
End Sub

[AlphaFrog]

You would concatenate the variable values within the formula string.

Something like this...

...[EuroValue]"", '[" & wbk2.FullName & "]" & Sheet & "'!R6C1,""[SalesLevel]...

....[RegionHierarchy].&[" & cell.Value & "]"")"

[TeloS53]

Thank you very much, but i still obtain this error :

Runtime error 1004
application-defined of object-defined error :/. I don't understand, because when i go to the debugger, i point on the wbk2.fullname and sheet and it shows the good parameters... I precise that i did not change the cell.value part

[TeloS53]

So, here is my new code :

Sub Button2_Click()
Dim cel As Range
Dim wbk1, wbk2 As Workbook
Dim sht1, sht2 As Worksheet
Set wbk1 = Application.ThisWorkbook
Set sht1 = wbk1.Sheets("MPC Business Figures")

MsgBox "Please, choose the source file which will be used in order to update data. It should be located in (G:) Department Share/ IT Services Europe/ Central Info/ MPC Business figures (Impact)"
    Sourcefile = Application.GetOpenFilename
    If Sourcefile <> False Then
       Workbooks.Open Filename:=Sourcefile
    Else
        MsgBox "No file has been selected"
        Exit Sub
    End If
Sheet = InputBox("Please type now the exact name of the sheet in which the information you want to paste here are coming from.")
Set wbk2 = ActiveWorkbook
Set sht2 = wbk2.Sheets(Sheet)
wbk1.Activate
sht1.Activate
Dim code As Range
For Each code In sht1.Range("B7:AA7").Cells
Cells(13, code.Column).FormulaR1C1 = "=GETPIVOTDATA(""[Measures].[EuroValue]"",'[" & wbk2.Name & "]" & sht2.Name & "'!R6C1,""[SalesLevel].[SalesLevelHierarchy]"",""[SalesLevel].[SalesLevelHierarchy].&[1]"",""[SetProducts]"",""[Product].[ProductHierarchy].&[2]"",""[SetAccounts]"",""[Account].[AccountId].&[26]"",""[SetRegions]"",""[Region].[RegionHierarchy].&[" & code.Value & "]"")"
Next code
End Sub

Still got that "Runtime error 1004
application-defined of object-defined error" :/ error, no one has an idea why ??