Copy cells if value in other cell matches with cell in another sheet

Kristina96

New Member
Hi there,

I need a macro that does the following:
In Sheet 2 column E there are ID numbers. The macro should look for those IDs in Sheet 1, column E.
When found it should copy values in the row of the ID from sheet 1 to sheet 2.
The values are in column C and D in sheet 1 and are supposed to be copied to columns B and C respectively.
Any help is very much appreciated.

Thank you and best regards
Kristina
 

Fluff

MrExcel MVP, Moderator
How about
Code:
Sub Kristina96()
   Dim Cl As Range
   Dim Ws1 As Worksheet, Ws2 As Worksheet
   
   Set Ws1 = Sheets("Sheet1")
   Set Ws2 = Sheets("sheet2")
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws1.Range("E2", Ws1.Range("E" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Cl.Offset(, -2).Resize(, 2).Value
      Next Cl
      For Each Cl In Ws2.Range("E2", Ws2.Range("E" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then Cl.Offset(, -3).Resize(, 2).Value = .Item(Cl.Value)
      Next Cl
   End With
End Sub
 

Kristina96

New Member
Thank you for the reply. The code is only copying the heading of the column though. Not the values.
I don't know if it is important but the ID numbers on sheet 1 start in B3 and the IDs in sheet 2 start in E5.
 

Fluff

MrExcel MVP, Moderator
In you op you said they were both in col E. do you still want to copy cols C:D to cols B:C?
 

Kristina96

New Member
Sorry, I had a typo in the OP.
So, I am trying to look at the values in column B of sheet 1. If column E of sheet 2 already contains that value, no action is required. If not, the value from column B, sheet 1 has to be copoied to column E of sheet 2. Additionally to copying the value itself. The text in colum C and D on sheet 1 should also be copied to column B and C on sheet 2.
Thank you very much and sorry for the confusion!
 

Fluff

MrExcel MVP, Moderator
Ok, how about
Code:
Sub Kristina96()
   Dim Cl As Range
   Dim Ws1 As Worksheet, Ws2 As Worksheet
   
   Set Ws1 = Sheets("sheet1")
   Set Ws2 = Sheets("sheet2")
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws1.Range("B2", Ws1.Range("B" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Cl.Offset(, 1).Resize(, 2).Value
      Next Cl
      For Each Cl In Ws2.Range("E2", Ws2.Range("E" & Rows.Count).End(xlUp))
         If .Exists(Cl.Value) Then .Remove Cl.Value
      Next Cl
      Ws2.Range("E" & Rows.Count).End(xlUp).Offset(1, -3).Resize(.Count, 2).Value = Application.Index(.Items, 0, 0)
      Ws2.Range("E" & Rows.Count).End(xlUp).Offset(1).Resize(.Count).Value = Application.Transpose(.Keys)
   End With
End Sub
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top