(Solved) – Copy the same range of cells from multiple Excel files using VBA


I have a macro that takes data from one Workbook and copies it into another Workbook.
Currently it is set that it copies files from Book2.xlsx into Book1.xlsm.
It only works if I have Book2 and Book1 open.
However, I would like to use this macro, so that it runs on all the Excel files in my folder, so for example if I have also Book5.xlsm, Book15.xlsx and Book153.xlsx in folder C:UsersJJDocumentsDownloads, I would like to copy and paste cells “D25:D26, D29:D32, D35” from all these files into Book1.xlsm.
How to automate that process, so that I do not have to manually enter the name of file in the code each time?
Thank you in advance for your help.

Sub Copy_Form_Below_Last_Cell() 
Dim wsDest As Worksheet
Dim lDestLastRow As Long
Set wsDest = Workbooks("Book1.xlsm").Worksheets("Sheet1")
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "H").End(xlUp).Offset(1).Row
Workbooks("Book2.xlsx").Worksheets("Sheet_2").Range("D25:D26, D29:D32, D35").Copy _
wsDest.Range("H" & lDestLastRow)     
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *