Find Value in Row, Copy and Paste

ktkelly_1

New Member
[FONT=&quot]I am wondering if anyone has ever used the Range.Find macro for finding a certain value storing it as a variable then looking for it again in another file.
[/FONT]

[FONT=&quot]
[/FONT]

[FONT=&quot]So basically there is an ID that I am trying to use as the finding variable and do this for each row. So first ID = Variable go to another workbook and look for that ID and copy two specific columns, lets say C and D then take the data within those two columns and past them in the corresponding workbook of the same row and the finding variable (ID)
[/FONT]

[FONT=&quot]
[/FONT]

[FONT=&quot]
[/FONT]

[FONT=&quot]WORKBOOK 1
[/FONT]

A
B
C
D
1
ID
TITLE
STATUS
OTHER
2
ABC-1234

<tbody>
</tbody>
[FONT=&quot]GO TO WORKBOOK 2, find the id in column a of another workbook
[/FONT]

A
B
C
D
20
ID
TITLE
STATUS
OTHER
21
ABC-1234
EXAMPLETITLE
STATUS1
--------

<tbody>
</tbody>
[FONT=&quot]Select Title and Status of the corresponding row (B21 and C21) and then bring it back to workbook 1
[/FONT]

A
B
C
D
1
ID
TITLE
STATUS
OTHER
2
ABC-1234
EXAMPLETITLE
STATUS1
--------

<tbody>
</tbody>
[FONT=&quot]Please let me know if I have lost everyone, I am not as familiar with looping which is why I hope for this to be a learning experience and for you to have pity on for asking how to create this. Thank you!!

Posted on Microsoft Community : https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_2016/find-value-in-row-copy-and-paste/beecf6d7-3610-4c13-a305-c4243426823c?tm=1562162391506[/FONT]
 

Fluff

MrExcel MVP, Moderator
A few questions
1) What are the names of the two workbooks & what are the sheet names?
2) Also will both workbooks be open?
3) where will the macro be stored?
 
Last edited:

ktkelly_1

New Member
Thank you so much for the quick reply

1. For now let's just call them workbook 1 and workbook 2 as noted above. The file names are a bit more complicated since they change
2. One of the workbooks won't be open, but I can manage that part of the coding wanted to focus on the actual process
3 for now I am running the macro in visual basic, may be moved to a button but unsure for now

Much Appreciated.
 

Fluff

MrExcel MVP, Moderator
This needs to go in Workbook1
Code:
Sub ktkelly1()
   Dim Wbk2 As Workbook
   Dim Ws1 As Worksheet, Ws2 As Worksheet
   Dim Cl As Range
   
   Set Ws1 = ThisWorkbook.Sheets("sheet1")
   Set Wbk2 = Workbooks("Book2.xlsx")
   Set Ws2 = Wbk2.Sheets("Sheet1")
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws2.Range("A2", Ws2.Range("A" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Cl.Offset(, 1).Resize(, 2)
      Next Cl
      For Each Cl In Ws1.Range("A2", Ws1.Range("A" & Rows.Count).End(xlUp))
         Cl.Offset(, 1).Resize(, 2).Value = .Item(Cl.Value)
      Next Cl
   End With
End Sub
 

Some videos you may like

This Week's Hot Topics

Top