$25.00 to amend VBA to copy additional data and create report

  • 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.

  • Thanks,

    Am I correct to assume that your House bill will always start at Row 13 and that there will be NO blank rows between data.

    In other words... in your example, there is data between rows 13 and 25.

    Would it be possible to have data between rows 13 to 20 and between 25 to 30?


  • Got it to run now, I had to remove a temp file path in the code. Was this correct? I think I am having an issue updating links in the files? Keeps stopping and asking about updating the links.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!