Update column in worksheet based on criteria with data from another worksheet

[jayped]

Hi there,

I'd like help creating a macro that allows me to essentially copy and paste information from one worksheet to another based on criteria that will save a great amount of time were it done manually. In the main worksheet I have a column labelled 'Reference' and a column labelled 'Statement Date' and the second worksheet would contain the Statement information which would have a reference column and a date column. I want the macro to update the "Statement Date' in the main worksheet with the date from the Statement tab based on the reference number in the main tab.


Thanks!!

 

[DanteAmor]

Try this, Just adjust the names of the sheets and the reference and date columns.

Sub Update_column()
  Dim ws1 As Worksheet, ws2 As Worksheet, c As Range, f As Range
  Set ws1 = Sheets("[COLOR=#ff0000]Main[/COLOR]")
  Set ws2 = Sheets("[COLOR=#ff0000]Sheet2[/COLOR]")
  For Each c In ws1.Range("[COLOR=#ff0000]A[/COLOR]2", ws1.Range("[COLOR=#ff0000]A[/COLOR]" & Rows.Count).End(xlUp))
    Set f = ws2.Range("[COLOR=#ff0000]A:A[/COLOR]").Find(c, , xlValues, xlWhole)
    If Not f Is Nothing Then
      ws1.Cells(c.Row, "[COLOR=#ff0000]B[/COLOR]") = ws2.Cells(f.Row, "[COLOR=#ff0000]B[/COLOR]")
    End If
  Next
End Sub

 

[jayped]

Thank you for your help!

Just one problem which I think lies at "If Not f Is Nothing" - I don't want dates that may have been entered previously to be deleted when I run the macro because the references can't be found on sheet 2. I just want blank cells to be updated and everything else remains as is. What would be the best way around this?

 

[jayped]

Also, can you provide something similar for a single worksheet if i had the same 2 worksheets in one side by side?

 

[DanteAmor]

Thank you for your help!

Just one problem which I think lies at "If Not f Is Nothing" - I don't want dates that may have been entered previously to be deleted when I run the macro because the references can't be found on sheet 2. I just want blank cells to be updated and everything else remains as is. What would be the best way around this?

Try this

Sub Update_column()
  Dim ws1 As Worksheet, ws2 As Worksheet, c As Range, f As Range
  Set ws1 = Sheets("Main")
  Set ws2 = Sheets("Sheet2")
  For Each c In ws1.Range("A2", ws1.Range("A" & Rows.Count).End(xlUp))
    Set f = ws2.Range("A:A").Find(c, , xlValues, xlWhole)
    If Not f Is Nothing Then

     [COLOR=#0000ff] if ws1.Cells(c.Row, "B") = "" then [/COLOR]ws1.Cells(c.Row, "B") = ws2.Cells(f.Row, "B")
    End If
  Next
End Sub

 

[jayped]

That worked. Thank you very much !

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.