Combine Worksheets in to one with new data

[Gmadd]

I send an excel file out to six different people everyday in an email and it contains a list of deliveries that were shipped late. The recipients are to chose a reason from a dropdown in the excel file and add any other comments in another column. When the recipient selects a reason from the dropdown I have a VBA that records the date and time it was entered. Now they send these files back to me and I need to combine them into a master file. What is the best way to combine this back into a master file retaining the actual date and time the reason was entered. I do not want to cut and paste the data as there are 100's of lines in each file.

 

[Adar123]

Try this code. It assumes that you run it from the "master file" into which you copy data in from the file received. Alternatively, you could run it directly in the Excel file you received.

It will calculate the number of rows with data received, open your original document and copy data one line below the original data. You may need to rework it a bit if you want to copy data from row 1.

If you need specific solutions, please share the extract of data and what exactly can be done and should not be done. Other may be able to help then.

'Save Transformed Data in combined file
    Dim Rng As Range, Cel As Range

    dTod = Format(Date, "yyyymmdd")
    With wkBk.Sheets("NewIncomingData")
        lastRow = .Range("A" & .Rows.Count).End(xlUp).Row ' calculate number of rows
        'range to copy
        Set Rng = wkBk.Sheets("NewIncomingData").Range("A2:E" & lastRow) ' choose columns to copy
    End With

'the workbook you want to update
    Set wkBk = Workbooks.Open("x:\\originalfile.xlsx")
    Set wkSht = wkBk.Sheets("1") 'enter the worksheet name that you want to copy data in
    lastRow = wkSht.Range("A" & wkSht.Rows.Count).End(xlUp).Row 'calculate last row in this file, remove if not needed
    'where to paste
    Set Cel = wkSht.Range("A" & lastRow + 1)
'copy and paste
    Rng.Copy Destination:=Cel