Finding row, then copy paste (macro)

[Edwin]

Hello,

I would like to create a macro that would use the “job number†located in
workbook 1 cell A1 and use it to find the row that contains the same job
number in a database (workbook 2). Job numbers are located in column A in
workbook 2. Then it will copy the contents of cell B1 from workbook 1 and
paste it in column H of the corresponding job number row in workbook 2.

Any help would be greatly appreciated.

Regards,

 

[Max]

Assume both Book1.xls and Book2.xls are open at the same time
Assume your data is in Sheet1 in both books
(you must always specify the sheetnames involved)

Then in Book2.xls,
in Sheet1,
place in H1: =VLOOKUP(A1,[Book1]Sheet1!A:B,2,0)
Copy H1 down to extract as required. Then freeze the results with an
"in-place" copy n paste special as values. Done.

For a neater look, you could use an ISNA error trap
to return blanks: "" for unmatched cases:
=IF(ISNA(VLOOKUP(..)),"",VLOOKUP(..))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Luke M]

Assuming you are on workbook 1 and on the sheet with job data you are looking
for, you could then run this code:

Sub CopyMacro()
On Error Resume Next
'Change address as appropriate
Workbooks.Open ("C:\My Documents\Book3.xls")
ThisWorkbook.Activate
'Change Name & Sheet as appropriate
For Each cell In Workbooks("Book3.xls").Sheets("Sheet1").Range("A:A")
If cell.Value = Range("A1").Value Then
cell.Offset(0, 7).Value = Range("B1").Value
Exit Sub
End If
Next cell
End Sub

 

[pol]

I tried ur formula but it is trying to open an excel sheet eventhough I
opened rebate.xls

With thanks

Assume both Book1.xls and Book2.xls are open at the same time
Assume your data is in Sheet1 in both books
(you must always specify the sheetnames involved)

Then in Book2.xls,
in Sheet1,
place in H1: =VLOOKUP(A1,[Book1]Sheet1!A:B,2,0)
Copy H1 down to extract as required. Then freeze the results with an
"in-place" copy n paste special as values. Done.

For a neater look, you could use an ISNA error trap
to return blanks: "" for unmatched cases:
=IF(ISNA(VLOOKUP(..)),"",VLOOKUP(..))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

I would like to create a macro that would use the “job number†located in
workbook 1 cell A1 and use it to find the row that contains the same job
number in a database (workbook 2). Job numbers are located in column A in
workbook 2. Then it will copy the contents of cell B1 from workbook 1 and
paste it in column H of the corresponding job number row in workbook 2

 

[Edwin]

This works great! Thanks Luke
--
Edwin

Assuming you are on workbook 1 and on the sheet with job data you are looking
for, you could then run this code:

Sub CopyMacro()
On Error Resume Next
'Change address as appropriate
Workbooks.Open ("C:\My Documents\Book3.xls")
ThisWorkbook.Activate
'Change Name & Sheet as appropriate
For Each cell In Workbooks("Book3.xls").Sheets("Sheet1").Range("A:A")
If cell.Value = Range("A1").Value Then
cell.Offset(0, 7).Value = Range("B1").Value
Exit Sub
End If
Next cell
End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

 

[Max]

Re-look carefully at your book names and/or sheetnames. There's probably a
stray whitespace (spacing) somewhere within the names -- not readily
apparent/visible to you, but which Excel will detect -- which is tripping
things up. Correct it, then re-trigger the formula (click inside the formula
bar, press ENTER), and it should all work fine.

P/s: You should always stay within your own thread. This keeps all
discussions where it should be, and it avails your thread to all responders
who might have something to offer.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik