我想要一個宏執行以下操作：打開一個新檔案（模板）并使用所選單元格行中的值填充其單元格。到目前為止，我創建了這個：
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
例如，如何從所選單元格的“G”列中提取資料？
uj5u.com熱心網友回復：
始終為范圍指定作業簿和作業表！否則，VBA 不清楚您期望此范圍的確切位置。
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
請注意，這Worksheets(1)是標簽欄中的第一個作業簿！您還可以使用Worksheets("Sheet1").
避免在 Excel VBA 中使用 Select。
// 根據評論編輯
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/349799.html