I need to update cell values from one sheet into cells on another sheet based on a reference cell

Hi ,

 I need to update cell values from one sheet  into cells on another sheet based on  a reference cell.  Total Excel noob!
I have a sheet that contain an ID number with a bunch of dates that I need to update cells on another sheet based on the ID number.
The ID number is the reference for both sheets!


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim sh As Worksheet
Dim rw As Integer
Dim RowCount As Integer
Dim lrow As Long
Dim srow As Integer
lrow = Cells(Rows.Count, 1).End(xlUp).Row

If Intersect(Target, Range("A1")) Is Nothing Then
Else

RowCount = 0

Set sh = ActiveSheet
For rw = 2 To lrow

srow = match(Sheets("origin_sheet").Cells(rw, 1).Value,Sheets("destination_sheet").range(F4:F10237),0)

  If srow  <> 0 Then
        Sheets("destination_sheet").Cells(rw, 4).Value = Sheets("origin_sheet").Cells(rw, 21).Value
   
  End If

Next rw

End If
End Sub


Thanks

Pete
Peter GrovesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
Can you attach an example file
0
Peter GrovesAuthor Commented:
Origin sheet contains newly updated dates!

Network |  User Status  |        created        |  Status             | approved  Status      | ready  Status |      completed Status                                                                                                                                                 
4232432       |   design         | 2017-08-11   |       2016-02-11   | 207-06-13                   |                                                         
10,000 other similar records as line above with different unique network numbers

                                                            
Destination sheet  is of similar makup  also with 10,000 records with the network numbers 95% same as origin numbers which is why
I can't just paste over the columns!

Network |  User Status  |        billed on       |  Status             | approved  Status      | ready  Status |      completed Status                                                                                                                                                 
4232432       |   design         | 2018-02-04 |       2016-02-11   | 207-06-13                   |  2018-03-27    |  2018-04-08                                          

I used the | to delimit the fields.

Hope this helps

Pete
0
Roy CoxGroup Finance ManagerCommented:
can you attach a small example workbook. I think AutoFilter with VBA might be what you need, but I need to see the sheet layout
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Roy CoxGroup Finance ManagerCommented:
I haven't helped you with any code
0
Peter GrovesAuthor Commented:
I've uploaded the file. Trimmed down to eliminate confidential stuff and reduce the file size as both sheets have of 10,000 entries. The file however still has 140.000 or so blank row which I've spent the last 30min trying to delete! So sorry about that!

But in a nutshell , I take the date entries from the origin file and upload to the destination master with the network number as reference!

thanks

Pete
EE3.xlsx
0
Peter GrovesAuthor Commented:
Yeah sorry, I must have hooked the solution button by accident!

Pete
0
Peter GrovesAuthor Commented:
I found this that works nicely!  From Barb Henderson on Youtube.
 

Sub findsomething()
Dim rng As Range
Dim account As String
Dim rownumber As Long
Dim x As Integer

For x = 2 To 20
account = Sheets("originaux").Cells(x, 1)
MsgBox ("text = ") & Sheets("originaux").Cells(x, 1)

Set rng = Sheets("Rap").Columns("F:F").Find(What:=account, _
    LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    rownumber = rng.Row
    MsgBox ("text = ") & Sheets("Rap").Cells(rownumber, 2).Value
   
   Next x


End Sub
0
Peter GrovesAuthor Commented:
Its posted above!

Pete
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
Well if your'e happy with that code then good, but on larger data sets AutoFilter would be much faster!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.