update data from multiple excel sheet

Question

I have problem on how to update data from multiple Excel sheets to one column in the master sheet. Whenever we enter a new row, I want it to automatically update in the master sheet. The value is id therefore it is unique across multiple worksheets. For example:

Excel sheet 1:

ID
---
1
2
3
4
5

Excel sheet 2:

ID
---
12
23
34
41
53

Excel sheet 3:

Master Excel sheet:

Whenever I enter new value like 6 (for worksheet1), it will be updated to master sheet.

Can I do that? Do we need to use macro? Thanks.

Update the code with SheetChange()

  With DataEntrySheet

     '** Set variables for using the Find method in a loop
    Set loopRng = DataEntrySheet.Columns(1)
    Set lookRng = MasterSheet.Columns(2)

     '** Set the range which will be used to write data if found
    Set OldLastCell = DoNotEditSheet.Range( _
    "C65536").End(xlUp).Offset(1)

     '** Start the Find loop
    For Each iCel In loopRng

         '** Using the Find method to find the cell value.
        Set foundRng = lookRng.Find(iCel.Value, lookRng.Cells(1), _
        xlValues, xlWhole, MatchCase:=True)

         '** Test if the variable 'foundRng' is Nothing.  If the value
         '   was not found, the variable will be Nothing, or else it
         '   will be the Range Object of that (first) found value.
        If foundRng Is Nothing Then

             '** Use the two ranges to transfer data (faster than Copy/Paste).
            OldLastCell.Value = iCel.Value
            OldLastCell.Offset(, -1).Value = iCel.Offset(, -1).Value
            OldLastCell.Offset(, 2).Value = iCel.Offset(, 1).Value
            OldLastCell.Offset(, 5).Value = iCel.Offset(, 2).Value
            OldLastCell.Offset(, 10).Value = iCel.Offset(, 3).Value

             '** Reset the variable to be one row below where we wrote the
             '   data to.  This will keep the data organized by rows.
            Set OldLastCell = OldLastCell.Offset(1)

        End If

         '** This MUST be set to Nothing before the next iteration.  If not,
         '   and a match is not found (following a good find) then you may
         '   have mismatched iterations with false results.
        Set foundRng = Nothing
    Next iCel
End With

End Sub

And they have to be entered in numerical order? So if 6 was added to worksheet 1, it would be added to the master excel sheet between 5 and 12? And if 6 was added to worksheet 2, nothing would happen? — LittleBobbyTables - Au Revoir
Yes it's possible and it's not that hard. What have YOU tried ? — ApplePie
Not necessarily. I have tried to use the VLOOKUP function on the master worksheet to reference it from difference worksheets, with no luck. — user1555907

Chuck Dickens Chuck Dickens · Accepted Answer · 2012-07-26T20:34:15

You can use the SheetChange() event to update the master worksheet, but this could slow down the data entry incredibly.

The SheetChange event will run your code everytime someone enters data and moves to another cell. Ideally you only want to check for new data when every column in the dataset is complete.

I would suggest using a button that you could press when the new row entry is complete and ready to be entered into the master sheet. There are numerous code samples on the web that will add unmatched rows to a new sheet.

update data from multiple excel sheet

1 Answers