Assuming that you want to copy data from
L24
,
H19
and
B29
cells in the source sheet into corresponding
B
,
C
and
D
cells in the destination sheet, try this:
srcWorksheet.Range("L24, H19, B29").Copy dstWorksheet.Range("B" & NRow)
For further information, please see:
Range.Copy method*[
^]
Another way is to refer each cell this way:
dstWorksheet.Range("B" & NRow) =srcWorksheet.Range("L24")
dstWorksheet.Range("C" & NRow) =srcWorksheet.Range("H19")
dstWorksheet.Range("D" & NRow) =srcWorksheet.Range("B29")
[EDIT]
I'd change it this way:
Option Explicit
Sub MergeSelectedWorkbooks()
Dim SrcBook As Workbook, DstBook As Workbook
Dim SrcSheet As Worksheet, DstSheet As Worksheet
Dim FolderPath As String
Dim SelectedFiles() As Variant
Dim NRow As Long, NFile As Long
Dim FileName As String
Set DstBook = Workbooks.Add(xlWBATWorksheet)
Set DstSheet = SummaryBook.Worksheets(1)
FolderPath = "C:\Users\Peter\invoices\"
ChDrive FolderPath
ChDir FolderPath
SelectedFiles = Application.GetOpenFilename( _
filefilter:="Excel Files (*.xl*), *.xl*", MultiSelect:=True)
NRow = 1
For NFile = LBound(SelectedFiles) To UBound(SelectedFiles)
FileName = SelectedFiles(NFile)
Set SrcBook = Workbooks.Open(FileName)
DstSheet.Range("A" & NRow).Value = FileName
DstShet.Range("B" & NRow) = SrcSheet.Range("L24")
DstShet.Range("C" & NRow) = SrcSheet.Range("H19")
DstShet.Range("D" & NRow) = SrcSheet.Range("B29")
NRow = NRow + 1
SrcWorkbook.Close savechanges:=False
Next NFile
DstSheet.Columns.AutoFit
DstBook.SaveAs FolderPath & "SummarySheet.xslx"
End Sub