Updating a worksheet from another worksheet

Phillip2 · Aug 5, 2019

I’m using office 365 and have a workbook with multiple worksheets. Worksheet 1 is a telephone directory that has a table 10135 rows x 20 columns. Worksheet 2 has a similar table 255 rows x 20 columns containing new information that I wish to use to update worksheet 1.

Column A in both worksheets are telephone numbers. These are my unique identifier for the data listed in each row.

I would like to use VBA to update Worksheet 1 by replacing the entire row for each matching telephone number that I have listed in Worksheet 2.

Joe4 · Aug 6, 2019

Welcome to the Board!

Try something like this:

Code:

Sub MyReplaceMacro()

    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lr As Long, r As Long, fr As Long
    Dim pnum As String
    Dim frng As Range
    
    Application.ScreenUpdating = False
    
'   Set first and second worksheet objects
    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")
    
'   Find last row with data in column on second sheet
    ws2.Activate
    lr = ws2.Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows in second sheet starting at row 2
    For r = 2 To lr
'       Get first phone number to find
        pnum = ws2.Cells(r, "A")
'       Find phone number on sheet 1
        Set frng = ws1.Columns("A:A").Find(What:=pnum, After:=ws1.Range("A1"), LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
'       See if phone number found
        If frng Is Nothing Then
            MsgBox "Cannot find phone number " & pnum & " from row " & r & " on main sheet"
'       Copy over
        Else
            fr = frng.Row
            ws2.Range(Cells(r, 1), Cells(r, 20)).Copy ws1.Cells(fr, 1)
        End If
    Next r
    
    Application.ScreenUpdating = True
    
    MsgBox "Copy complete"
    
End Sub

I documented my code, which should help you follow along, and hopefully make any minor adjustments you may need to make.

My Aswer Is This · Aug 6, 2019

Try this:

Code:

Sub Check_Register()
'Modified  8/6/2019  8:32:54 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim lastrow As Long
lastrow = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row
Dim lastrowa As Long
lastrowa = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
Dim SearchString As String
Dim SearchRange As Range
For i = 1 To lastrow
SearchString = Sheets(2).Cells(i, 1).Value
Set SearchRange = Sheets(1).Range("A1:A" & lastrowa).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then
Else
r = SearchRange.Row
Sheets(2).Rows(i).Copy Sheets(1).Rows(r)
End If
Next
Application.ScreenUpdating = True
End Sub

dmt32 · Aug 6, 2019

Hi,
untested but another idea perhaps

Code:

Sub UpdateDirectory()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim datarange As Range, cell As Range
    Dim m As Variant
    
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
    
    Set datarange = ws2.Range("A1").CurrentRegion
    
    For Each cell In datarange.Rows
        m = Application.Match(ws2.Cells(cell.Row, 1).Value, ws1.Columns(1), 0)
        If Not IsError(m) Then datarange.Rows(cell.Row).Copy ws1.Cells(CLng(m), 1)
    Next cell
End Sub

Dave

Phillip2 · Aug 6, 2019

Thank you all so much for the quick responses. I think thatthis is going to work. I really appreciate everyone’s help.

My Aswer Is This · Aug 6, 2019

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Phillip2 said:
Thank you all so much for the quick responses. I think thatthis is going to work. I really appreciate everyone’s help.

Phillip2 · Aug 6, 2019

Thanks

Phillip2 · Aug 6, 2019

I have another similar request----

In the first issue above I had a couple of hundred numbersto change at once. However, most of the time I only have a couple or so to doat a time. Instead of editing my directory (worksheet1) directly I want toupdate it from a form on a separate worksheet (worksheet2).

I have a table with 5 columns and 20 rows. I want to entertelephone numbers in column A and then have a macro that will populate the rowswith the current data from worksheet 1.
After I make the needed changes to the form, I’ll run the previous macro that I have and update worksheet1.

My Aswer Is This · Aug 6, 2019

I thought that was what your previos post was doing.
And when you say you have a Form.

You would have to provide the Form Range

And you said:

I have a table with 5 columns and 20 rows.

If your talking about a Excel Table then we need to know the Table name

A Table is a Range on a sheet.

Phillip2 said:
I have another similar request----

In the first issue above I had a couple of hundred numbersto change at once. However, most of the time I only have a couple or so to doat a time. Instead of editing my directory (worksheet1) directly I want toupdate it from a form on a separate worksheet (worksheet2).

I have a table with 5 columns and 20 rows. I want to entertelephone numbers in column A and then have a macro that will populate the rowswith the current data from worksheet 1.
After I make the needed changes to the form, I’ll run the previous macro that I have and update worksheet1.

Phillip2 · Aug 7, 2019

I'm sorry. I used the wrong terminology. This is anotherworksheet. Call it worksheet3. The range is A3:T8.
This is similar to what I was doing before. Thedifference is that before I was making a huge blanket change to mydatabase. However, on this worksheet I want to enter a telephone number and ithave a macro pull that information from my directory (worksheet 1). Then I’llmake any changes and use the other macro to update worksheet1.

Updating a worksheet from another worksheet

Phillip2

Board Regular

Excel Facts

Joe4

MrExcel MVP, Junior Admin

My Aswer Is This

Well-known Member

dmt32

Well-known Member

Phillip2

Board Regular

My Aswer Is This

Well-known Member

Phillip2

Board Regular

Phillip2

Board Regular

My Aswer Is This

Well-known Member

Phillip2

Board Regular

Similar threads

Forum statistics

Share this page

Updating a worksheet from another worksheet

Board Regular

Excel Facts

MrExcel MVP, Junior Admin

Well-known Member

Well-known Member

Board Regular

Well-known Member

Board Regular

Board Regular

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock