CODEWITHSUNDEEP
excelvba
user1555907
user1555907

Reputation: 1

update data from multiple excel sheet

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:

ID
---
123
215
324
445
562

Master Excel sheet:

ID
---
1
2
3
4
5
12
23
34
41
53
123
215
324
445
562

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

Upvotes: 0

Views: 2958

Answers (1)

Chuck Dickens
Chuck Dickens

Reputation: 329

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.

Upvotes: 0

Related Questions