0

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?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Dark Archon
  • 71
  • 6

1 Answers1

1

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
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • What I'd like to achieve: I select a row by selecting one of its cell. Pressing macro button (report) opens a new template and - for example - gets the same row's data from column "F" and puts it into the opened template's specific cell. So, OpenedWb.Worksheets(1).Range("E11:I11").FormulaR1C1 = "=[makroproba.xlsm]Munka1!R4C6" should look like OpenedWb.Worksheets(1).Range("E11:I11").FormulaR1C1 = "=[makroproba.xlsm]Munka1![selectedrow's "F" column data]" – Dark Archon Nov 04 '21 at 11:14
  • @DarkArchon That would be a bit different. See my edit. – Pᴇʜ Nov 04 '21 at 11:28
  • Can I ask for a little modification? In this way, the whole cell gets copied, including value, cellformat and everything. I only need its value, the rest of them not needed. Thanks! – Dark Archon Nov 05 '21 at 12:53
  • @DarkArchon What you describe cannot be correct, the formula can only pull a value but no format! – Pᴇʜ Nov 05 '21 at 12:55
  • To be more precise, it seems it deletes the original cell's category as general. Selection.NumberFormat = "General" must be applied after inserting values for some reason. – Dark Archon Nov 08 '21 at 06:35
  • @DarkArchon this is because Excel "thinks". You can see the same happening when you manually enter a date into a cell. It automatically switches from general to date. You will probably have to deal with that as Microsoft thinks this is a feature not a bug. – Pᴇʜ Nov 08 '21 at 06:50
  • @PEH I'd like to make the filename and sheet name to be general, so if I rename the file or change the first sheet's name won't affect the script. `code` OpenedWb.Worksheets(1).Range("D2:E2").FormulaR1C1 = "=[" & mainFilename & "]" & mainSheet & "!" & SelectedData.EntireRow.Cells(1, "G").Address(ReferenceStyle:=xlR1C1) Of course, I have defined mainFilename and mainSheet as strings. It does work, but is there a nicer solution? – Dark Archon Nov 08 '21 at 10:33
  • @DarkArchon no, everything good with that solution, looks nice to me. – Pᴇʜ Nov 08 '21 at 10:53
  • @PEH How can I do this for every selected row? Now it works only for one. It would be awesome if I select more cells, and the macro generated every file. Thanks! – Dark Archon Nov 09 '21 at 08:39