Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding row, then copy paste (macro)
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, -- Edwin |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding row, then copy paste (macro)
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 --- "Edwin" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding row, then copy paste (macro)
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!* "Edwin" wrote: 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, -- Edwin |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding row, then copy paste (macro)
I tried ur formula but it is trying to open an excel sheet eventhough I
opened rebate.xls With thanks "Max" wrote: 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 --- "Edwin" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding row, then copy paste (macro)
This works great! Thanks Luke
-- Edwin "Luke M" wrote: 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!* "Edwin" wrote: 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, -- Edwin |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding row, then copy paste (macro)
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 --- "pol" wrote: I tried ur formula but it is trying to open an excel sheet even though I opened rebate.xls With thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and Paste Macro | Excel Discussion (Misc queries) | |||
macro to copy and paste | Excel Discussion (Misc queries) | |||
copy and paste using macro | Excel Discussion (Misc queries) | |||
Copy/Paste Macro | Excel Discussion (Misc queries) | |||
Should I use a macro to copy and paste value? | Excel Worksheet Functions |