Hi
@XcelNoobster.
Thanks for posting on MrExcel.
According to the above we have 2 books.
Consider the following:
1. In a third workbook put the macro and save this third workbook as excel enable macros.
2. In the macro I have named the 2 books as "Book 1.xlsx" and "Book 2.xlsx".
3. In the macro you must put the real names of your books, according to the name of the sheets, in these lines of the macro:
Rich (BB code):
Set wb1 = Workbooks("book 1.xlsx") 'This workbook contains sheet "Report1"
Set wb2 = Workbooks("book 2.xlsx") 'This workbook contains sheet "Sheet1"
4. Both books must be open.
5. According to your third point:
Then, in "Book 1" a new sheet will be created with the name "Final Result"
6. You can change the name of the "Final Result" sheet in these lines of the macro:
Rich (BB code):
On Error Resume Next: wb1.Sheets("Final Result").Delete: On Error GoTo 0
sh3.Name = "Final Result"
7. Put the macro in a module and make the changes mentioned above.
VBA Code:
Sub finds_value()
Dim wb1 As Workbook, wb2 As Workbook
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
Dim f As Range
Dim i As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb1 = Workbooks("book 1.xlsx") 'This workbook contains sheet "Report1"
Set wb2 = Workbooks("book 2.xlsx") 'This workbook contains sheet "Sheet1"
Set sh1 = wb1.Sheets("Report1")
Set sh2 = wb2.Sheets("Sheet1")
sh1.Copy after:=wb1.Sheets(wb1.Sheets.Count)
Set sh3 = wb1.Sheets(wb1.Sheets.Count)
On Error Resume Next: wb1.Sheets("Final Result").Delete: On Error GoTo 0
sh3.Name = "Final Result"
sh3.Range("A:A").Insert
sh3.Range("A1").Value = "ECR"
For i = 2 To sh3.Range("B" & Rows.Count).End(3).Row
Set f = sh2.Range("F:F").Find(sh3.Range("B" & i).Value, , xlValues, xlWhole, , , False)
If Not f Is Nothing Then
sh3.Range("A" & i).Value = sh2.Range("A" & f.Row).Value
Else
sh3.Range("A" & i).Value = "NONE"
End If
Next
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
--------------
Note XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here:
XL2BB Add-in
Note that there is also a "
Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
| book 1.xlsx |
|---|
|
|---|
| A | B | C |
|---|
| 1 | Item number | Item desc | Level |
|---|
| 2 | 44 | con | 1 |
|---|
| 3 | 66 | front | 2 |
|---|
| 4 | 34 | insu a | 3 |
|---|
| 5 | 33 | insu b | 4 |
|---|
|
|---|
| book 2.xlsx |
|---|
|
|---|
| A | B | C | D | E | F |
|---|
| 1 | Number | Desc | St | Workflow | Change | Item Number |
|---|
| 2 | Test1 | | | | | 44 |
|---|
| 3 | Test2 | | | | | 55 |
|---|
| 4 | Test3 | | | | | 66 |
|---|
|
|---|
Final:
| book 1.xlsx |
|---|
|
|---|
| A | B | C | D |
|---|
| 1 | ECR | Item number | Item desc | Level |
|---|
| 2 | Test1 | 44 | con | 1 |
|---|
| 3 | Test3 | 66 | front | 2 |
|---|
| 4 | NONE | 34 | insu a | 3 |
|---|
| 5 | NONE | 33 | insu b | 4 |
|---|
|
|---|
--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------
