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

  • 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