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.

My Aswer Is This · Aug 7, 2019

I guess it's just always hard for me to understand.
You said:

I want to enter a telephone number and ithave a macro pull that information from my directory (worksheet 1)

Enter a Telephone number Where?

pull that information from my directory (worksheet 1)

Pull what information?
Do you mean the entire Row

And what does the range A3 to T8 have to do with this.

_{^{<strike>

</strike>}}

Phillip2 · Aug 8, 2019

Thank you for trying to help. I apologize for any confusion. Let’s just start over. I’m using office 365 and have aworkbook with multiple worksheets. I have a worksheet (worksheet1) which is atelephone directory. It is a table 10135 rows x 20 columns. Then in anotherworksheet (worksheet 3) I have another table that is 5 rows x 20 columns.

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

What I want to do is to enter a telephone number(s) intocolumn A of worksheet 3 and have a macro that will populate the rest of the rowusing the data associated with that number in worksheet1. (To answer yourquestion. Yes, the entire row.)

Then my plan from here is twofold. One I can either just viewthe information, or two I can edit the information. If I choose to edit it, Ialready have a working macro that will take the information in this table andupdate worksheet1.

My Aswer Is This · Aug 8, 2019

So in worksheet (3) you want to enter several telephone numbers in Column A starting in Row(2)

Then you want to search Sheet(1) column A for that particular phone number and copy that entire row back into Sheet(3)

Is that what you want for the first part?

I wish you would stop using the term "Table"

You mentioned in a previous post it's not a Table it's a sheet.

A Table is a Range on a Sheet. Look on the Ribbon and you will see insert Table
A Table has a name like Table1 or any other name you want to give it.

A Table and a sheet are not the same.

A Table might be Named "Alpha" which is on a sheet named "Bravo"

Using improper terminology can get us confused

And it is always best to give all sheets a name like:

"Alpha" "Bravo" "Charlie"

Instead of referring to sheet as Sheet(1) Sheet(2) or Sheet(3)

So if you can answer my questions be sure and answer all of them I will try and help you.

Phillip2 · Aug 8, 2019

Yes that is what I am looking for. If you want to call it a worksheet that is fine. However, it is a TABLE inserted on that worksheet.

My Aswer Is This · Aug 8, 2019

Try this:

Run this script from sheet(3)

Have you Phone numbers in Column(A)

Code:

Sub Search_Me()
'Modified  8/8/2019  10:01:08 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim r As Long
Dim lastrow As Long
Dim lastrowa As Long
Sheets(3).Activate
lastrowa = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
lastrow = Sheets(3).Cells(Rows.Count, "A").End(xlUp).Row
Dim SearchString As String
Dim SearchRange As Range
    For i = 1 To lastrow
        SearchString = Sheets(3).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(1).Rows(r).Copy Sheets(3).Rows(i)
            End If
    Next
Application.ScreenUpdating = True
End Sub

Phillip2 · Aug 8, 2019

Thank you so much! I really appreciate your help.

My Aswer Is This · Aug 8, 2019

So I assume I have now provided all you need.

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

Phillip2 said:
Thank you so much! I really appreciate your help.

Phillip2 · Aug 8, 2019

Yes you did. Thank you again!

My Aswer Is This · Aug 8, 2019

I now see you just joined the forum this week. Glad to see you joined us. Look forward to helping you next time. I hope you try reading the code and can understand some of it so you will be able to write your own scripts as you learn more.

I believe in the old phase:

It's better to teach a Man to Fish then to always Fish for him.
Take care.

Phillip2 said:
Yes you did. Thank you again!

Updating a worksheet from another worksheet

Phillip2

Board Regular

My Aswer Is This

Well-known Member

Excel Facts

Phillip2

Board Regular

My Aswer Is This

Well-known Member

Phillip2

Board Regular

My Aswer Is This

Well-known Member

Phillip2

Board Regular

My Aswer Is This

Well-known Member

Phillip2

Board Regular

My Aswer Is This

Well-known Member

Similar threads

Forum statistics

Share this page

Updating a worksheet from another worksheet

Board Regular

Well-known Member

Excel Facts

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

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