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) wsDest.Activate End Sub