How to copy non adjacent cells data from multiple workbooks and paste into a master workbook automatically using VBA.
We receive multiple excel files with unsorted data without any headers and we need to select some specific data from those workbooks, close that workbook and paste it into the Master sales workbook under some specific headers; then again open another workbook and repeat the procedure over again.
Even though the data is unsorted, the position for each data is very specific and does not change in any worksheet, Can this process be automated using VBA? Please note all these excel files are stored in same folder and with the master sales workbook. Watch the video below:
Watch this video on YouTube.
Here’s the complete VBA code to copy non adjacent cells data from multiple workbooks and paste in master workbook automatically:
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 <> “”
Workbooks.Open (path & myFile)
Set copyrange = Sheets(“sheet1”).Range(“E9,C13,B7,E3,B18”)
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
col = 1
For Each cel In copyrange
Cells(erow, col).PasteSpecial xlPasteValues
col = col + 1
myFile = Dir()
Application.ScreenUpdating = True