How to pull a separate field with range of data RRS feed

  • Question

  • I am trying to pull customer name from cell E3 from multiple excel workbooks along with invoicing milestones from H59:I75.  I can get the invoicing milestone data to pull through although I would like to know how to skip blank records in the range and am seeing one extra row at the end that I don't want.  I don't know how to get the company name to appear before each of the invoice milestone rows.  I need to take this milestone data and then consolidate in excel to have invoice amount show in the appropriate fiscal month column.

    I have two excel files along with master excel file with VBA code to pull invoicing milestone data that can be shared on one-drive.  I also have example of how I'm trying to slot the amounts into a new worksheet column based on invoice YYYY-MM.  

    Option Explicit
    Sub CopyDataFromMultipleWorkbooksIntoMaster()
        ' ---
        Dim lastRow As Long
        ' --- define folder path where source files exist ** change drive letter [G]=>[C]
        Dim folderPath As String, filepath As String, fileName As String
        folderPath = "C:\Users\chris\Documents\GSI Documents\Project Health-Sprint Reports\Project Charter Workbooks\New\"
        ' --- check if folder exists
        If (Dir(folderPath, vbDirectory) = "") Then
            MsgBox "Folder Not Exist" & Chr(13) & folderPath
            Exit Sub
        End If
        ' --- DIR function
        filepath = folderPath & "*.xls*"
        fileName = Dir(filepath)
        Do While fileName <> ""
            ' --- open a source file: it will be active
            Workbooks.Open (folderPath & fileName)
            ' --- Copy&Paste makes performance low: do not use it
            With ThisWorkbook.Sheets("Master")
                lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
                'MsgBox "lastRow = " & lastRow  ' -- for debugging
                .Range("A" & lastRow & ":H" & lastRow + 17).Value _
                    = ActiveSheet.Range("B59:I75").Value
             ' ---   .Range("A" & lastRow + 5 & ":J" & lastRow + 9).Value _
                    = ActiveSheet.Range("B66:K70").Value
                ' --- font color = black (original header row is font color=white)
                .Range("A" & lastRow & ":H" & lastRow + 17).Font.Color = vbBlack
                ' --- for confirmation
                .Range("A" & lastRow & ":H" & lastRow + 17).Borders.LineStyle = xlContinuous
            End With
            ' --- close the source file without saving
            ActiveWorkbook.Close saveChanges:=False
            ' ---
            fileName = Dir()
        ' ---
        MsgBox "CopyData completed!!"
    End Sub

    Example of customer name in cell E3 above

    Example of Invoice milestone data B59:I75

    Example of how I'm trying to get data to show after running VBA code


    • Edited by Cpdille Thursday, November 21, 2019 2:35 PM
    Wednesday, November 6, 2019 4:59 PM