Find Value in Row, Copy and Paste

ktkelly_1

New Member
Joined
Jun 13, 2019
Messages
17
[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...-3610-4c13-a305-c4243426823c?tm=1562162391506[/FONT]
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top