Uploaded August 2019 | Updated September 2026, 2 weeks ago

---

How to copy non adjacent cells data from multiple workbooks and paste into a master workbook automatically using VBA.

Here's the complete VBA code. Angular brackets are replaced with 'Not Equal To':
Sub copyNonAdjacentCellsData()
Dim myFile As String, path As String
Dim erow As Long, col As Long

path = "c:\copy-non-contiguous-cells-data\"
myFile = Dir(path & "*.xlsx")

Application.ScreenUpdating = False

Do While myFile Not Equal To ""
Workbooks.Open (path & myFile)
Windows(myFile).Activate

Set copyrange = Sheets("sheet1").Range("E9,C13,B7,E3,B18")

Windows("master-wbk.xlsm").Activate

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

col = 1
For Each cel In copyrange
cel.Copy

Cells(erow, col).PasteSpecial xlPasteValues

col = col + 1

Next

Windows(myFile).Close savechanges:=False
myFile = Dir()
Loop
Range("A:E").EntireColumn.AutoFit

Application.ScreenUpdating = True

End Sub

Complete details available at our website: exceltrainingvideos.com/copy-non-adjacent-cells-data-from-multiple-workbooks