Solved: Search and copy question

[brenton]

I use two reports at work that contain information about inventory. The common denominator between the two sheets is the lot ID number. I need to copy a value from the second sheet to the first sheet for each lot ID number. I am thinking that this should be fairly simple, but am still a VBA novice and would appreciate any advice on how to do this anyone can provide.
Thanks!

[Ken Puls]

Hi brenton, and welcome to VBAX.

Are you sure you need VBA for this? You can't just put a vlookup formula in the second sheet to look up the value from the first sheet? It would update live that way...

[ElvisFan]

Hello, I am new on here also. I am not very good in VBA but I am learning. Yes a Vlookup would work. Assuming you always keep the same sheets. If he always gets new worksheets everytime, this would work. I know there are probably better ways of doing this. But this would work.
If this is too basic or too rough please let me know. I am self taught, so I don't always know the best ways.

Sub CopyData()
Dim Book1a As Variant, Book2a As Variant
'Book1 has the source data. The number you want to copy is in the next column over. column B
'Looks in Book2 file for the 1st number.
Windows("book2").Activate
For Each Book2a In Range("a2:a10")
'Looks in Book1 for cooresponding number
Windows("book1").Activate
For Each Book1a In Range("a2:a10")
'When it finds a match, it copies from book1 to book2
If Book1a = Book2a Then
Book1a.Offset(0, 1).Copy
Book2a.Offset(0, 1).PasteSpecial (xlValues)
End If
Next Book1a
Next Book2a
End Sub

[Charlize]

Does this suite your needs ? Sheet Book1 is active when you run this thing.
Sub CopyData()
'define the ranges
Dim Dest, lookupvalues, cell As Range
'number of rows
Dim Drow, Lrow As Long
'the value you were searching for
Dim searchvalue As String
Drow = Worksheets("Book1").Range("A" & Rows.Count).End(xlUp).Row
Lrow = Worksheets("Book2").Range("A" & Rows.Count).End(xlUp).Row
Set Dest = Worksheets("Book1").Range("A2:A" & Drow)
Set lookupvalues = Worksheets("Book2").Range("A2:A" & Lrow)
'for every value in column A in destination range
'look for a match in lookupvalues range
'put the value of the column besides the value you found
'in searchvalue
For Each cell In Dest
searchvalue = lookupvalues.Find(cell).Offset(0, 1)
'write value you found in Book1 column B
cell.Offset(0, 1).Value = searchvalue
Next cell
End Sub
Charlize

[mdmackillop]

If this is too basic or too rough please let me know. I am self taught, so I don't always know the best ways. Hi ElvisFan,
Welcome to VBAX.
Nice to see you jumping in with a solution, and it's a fair attempt.
Regards MD


A few comments.
The OP mention Worksheets. It's fair to assume that they are in the same workbook.
Variant should be used for arrays etc. and truly variant items. If you know the type, this should be used.
Keep ranges dynamic where possible; it keeps the code flexible. My range will find the last used cell in Column 1
Learn how to use Find; it's much quicker than checking each element in a column of say 30,000 cells. Once you know it, you'll use it all the time.
No need to PasteSpecial here.

Sub CopyData2()
Dim cel As Range, c As Range
'Sheet2 has the source data. The number you want to copy is in the next column over. column B
'Search in Sheet2 for each cell value from sheet1
For Each cel In Range(Sheets(1).Cells(2, 1), Sheets(1).Cells(Rows.Count, 1).End(xlUp))
'Looks in Sheet2 for coresponding number
Set c = Sheets(2).Columns(1).Find(what:=cel.Value, lookat:=xlWhole)
If Not c Is Nothing Then c.Offset(0, 1).Copy cel.Offset(0, 1)
Next cel
End Sub

[ElvisFan]

Thanks for the information. I have never thought of using a dynamic range like that. I have usually used a range name instead of cell addresses. That is sort of dynamic. What I usually do is use the same sort of code to set a range name. Then I use the range name. That is dynamic. As I said, not pretty but usually gets the job done. But it is 2 steps. I didn't know that find could be used like that. I will remember that one. I always got into the habit of doing the Paste Special. Even though I know that the space after the copy is the destination. Obviously, I should go through the options when I Dim a variable. Range never dawned on me.
Thanks for the help. I used Macros in Lotus 123. When we switched I just started reading.