Getting a value from a selected cell's row-CodePudding

I'd like a macro what does the following: opens a new file (template) and fills its cells with values from the selected cell's rows. So far, I created this:

Sub CreateReport()
'
' 
'

'
    Workbooks.Open Filename:= _
        "D:\_munka_\E6645\Egyéni Office-sablonok\megf_nyil_sablon.xltx", Editable:= _
        True
    Range("E11:I11").Select
    ActiveCell.FormulaR1C1 = "=[makroproba.xlsm]Munka1!R4C6"
    Range("E11:I11").Select
End Sub

How can I pull data from the selected cell's column "G" for example?

CodePudding user response：

Always specify a workbook and worksheet for ranges! Otherwise it is not clear for VBA where exactly you expect this range to be.

Option Explicit

Sub CreateReport()
    Dim OpenedWb As Workbook  'set the workbook to a variable so we can reference it
    Set OpenedWb = Workbooks.Open(Filename:= _
        "D:\_munka_\E6645\Egyéni Office-sablonok\megf_nyil_sablon.xltx", Editable:=True)

    'referece which workbook and worksheet you mean
    OpenedWb.Worksheets(1).Range("E11:I11").FormulaR1C1 = "=[makroproba.xlsm]Munka1!R4C6"
    
    ' pull value from another workbook's cell
    OpenedWb.Worksheets(1).Range("A1").Value =  ThisWorkbook.Worksheets("yoursheetname").Range("G1").Value
End Sub

Note that Worksheets(1) is the first workbook in the tab bar! You can also reference a workbook by its tab name using Worksheets("Sheet1").

Avoid using Select in Excel VBA.

// edit according comment

Option Explicit

Sub CreateReport()
    'first remember what was selected as `Selection` quickly changes when you open other workbooks!
    Dim SelectedData As Range
    Set SelectedData = Selection

    Dim OpenedWb As Workbook  'set the workbook to a variable so we can reference it
    Set OpenedWb = Workbooks.Open(Filename:= _
        "D:\_munka_\E6645\Egyéni Office-sablonok\megf_nyil_sablon.xltx", Editable:=True)

    'referece which workbook and worksheet you mean
    OpenedWb.Worksheets(1).Range("E11:I11").FormulaR1C1 = "=[makroproba.xlsm]Munka1!" & electedData.EntireRow.Cells(1, "F").Address(ReferenceStyle:=xlR1C1)
End Sub