# VLOOKUP/MATCH help needed

#### shina67

##### Board Regular
Hi,
Hope someone can help. I have a excel sheet sent to me on a daily basis, this sheet changes daily. I alter this sheet over the course of the working day. I have uploaded the sheet to the following link https://www.dropbox.com/s/zrmcxnbey5...rder.xlsx?dl=0. The following sheet is what it looks like after I have altered things https://www.dropbox.com/s/risd3d4bbh...%201.xlsx?dl=0. If the numbers in column F & G on sheet "Book 1" can be found in columns F & G in "Doorsets Open Order" then I need the colour coding copied from "Book 1" to "Doorsets Open Order". The numbers in column F & G will eventually disappear as the order is shipped out to my customer. Basically the sheet "Doorset Open Order" is a sheet with all my outstanding customer orders and sheet "Book 1" is what I save to let myself know where we are as a department with the order.
Hope someone can help as this will save me hours of work everyday transferring data from "Book 1" to "Doorsets Open Order".
Hope I have explained correctly what I require.

Regards
Sean

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

bump

#### jorismoerings

##### Well-known Member
Hi,

You're in quite a hurry to bump after 4 hours.
What you've asked can't be done by a VLOOKUP or a MATCH formula.
You will need to insert in a macro which will compare all lines and copy the format to the new sheet if they match.
Looking at the number of lines to compare it will be somewhat time consuming but this will work.

Be careful! Your file lay out weren't equal in both files. I deleted column A in the Doorset Open Order File to make them equal!

Code:
``````Sub FindMatch()

Dim i, j, Rows1, Rows2 As Integer
Dim OrderNoS, OrderNoD, LineNoS, LineNoD As String
Dim sourceRange, destinationRange As Excel.Range

Rows1 = Workbooks("Book 1.xlsx").Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row             'rows count in Source Sheet
Rows2 = Workbooks("Doorset Open Order.xlsx").Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row 'rows count in Destination Sheet

'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

'Loop Through Rows
For i = 1 To 10
OrderNoS = Workbooks("Book 1.xlsx").Worksheets("Sheet1").Cells(i, "F").Value                    'Order Number  Source Sheet
LineNoS = Workbooks("Book 1.xlsx").Worksheets("Sheet1").Cells(i, "G").Value                     'Line Number Source Sheet

For j = 1 To 10
OrderNoD = Workbooks("Doorset Open Order.xlsx").Worksheets("Sheet1").Cells(j, "F").Value    'Order Number Destination Sheet
LineNoD = Workbooks("Doorset Open Order.xlsx").Worksheets("Sheet1").Cells(j, "G").Value     'Line Number Destination Sheet

If (OrderNoS = OrderNoD) And (LineNoS = LineNoD) Then   'Compare Rows
Set sourceRange = Workbooks("Book 1.xlsx").Worksheets("Sheet1").Rows(i)
Call sourceRange.Copy
Set destinationRange = Workbooks("Doorset Open Order.xlsx").Worksheets("Sheet1").Rows(j)
Call destinationRange.PasteSpecial(xlPasteFormats)
End If

Next j

Next i

'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub``````

Last edited:

#### shina67

##### Board Regular
Hi jorismoerings,

I cannot seem to get your code to work.
Is it a standalone vba or is it inserted in either the Doorset Open Order.xlsx or the Book 1.xlsx.

thanks so far for your help.

Replies
0
Views
230
Replies
4
Views
332
Replies
2
Views
118
Replies
1
Views
179
Replies
0
Views
328

1,195,918
Messages
6,012,297
Members
441,690
Latest member
CyberWrek

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back