I use the following VBA to open each file in a specified location and copy certain data from each file to a report.
- Sub t()
- Dim wb As Workbook, sh As Worksheet, ary As Variant, fPath As String, fName As String, i As Long, rw As Long
- fPath = "X:\SEA Shares\warehouse\CFS and FMM Program\SEA Devanned January-2020\"
- Set sh = Workbooks("New").Sheets(1)
- ary = Array("C3", "C4", "C5", "H2", "H3", "H4")
- fName = Dir(fPath & "*.xls*") Do While fName <> "" If fName <> ThisWorkbook.Name Then Set wb = Workbooks.Open(fPath & fName) For i = 2 To 7 rw = sh.Cells(Rows.Count, 2).End(xlUp)(2).Row sh.Cells(rw, i) = wb.Sheets(1).Range(ary(i - 2)).Value Next wb.Close False End If fName = Dir Loop
- End Sub
I need it to also copy from each file, when opened, additional data and save as shown, in the example attached. all files opened will be the same format. They will all have a different number of House Bills, But row 35 would be the last row used in each file. I only want it to copy the rows with data.
The data from each file's cells noted in red. Please let me know if that makes sense. Thanks for your help.
Please let me know questions or comments.