Match data from multiple w/books & paste row Closed

Question

Hello, 

I have a main spreadsheet which contains order reference numbers (Column B), details of the order are filled out to the right on the same row.

 I have 3 identical spreadsheets (for 3 members of staff), they each fill out their own orders on their spreadsheet. 

I want their data to be pulled in to the main spreadsheet using a macro. It should look at Column B in the main spreadsheet for order reference numbers; When the reference numbers in this column match to Column B of one of the 3 staff spreadsheets, data in Columns H:R in the staff spreadsheet should be copied to the corresponding columns in the main spreadsheet. 

the order reference numbers are unique to the staff members so there shouldn't be an issue with multiple matches. 

I'm new to VBA/Macros and haven't been successful trying to adapt existing codes i've found to work. I'm in the process of reading up on VBA/Macros but need to get this up and running asap for work. 

Any help would be greatly appreciated! 

Thanks, Kev

Configuration: Windows 7 / Safari 535.2

venkat1926 · Answer

you mean multiple worksheets and not workbooks

see sample file in this webpage

http://speedy.sh/DaJVB/kev.sls.xls


try this macro 

Sub test()
Dim j As Long, cfind As Range, r As Range, c As Range
For j = 1 To Worksheets.Count
If Worksheets(j).Name = "main" Then GoTo nextj
With Worksheets(j)
Set r = Range(.Range("B2"), .Range("B2").End(xlDown))
For Each c In r
Set cfind = Worksheets("main").Columns("B:B").Find(what:=c.Value, lookat:=xlWhole)
If Not cfind Is Nothing Then
Range(.Cells(c.Row, "H"), .Cells(c.Row, "R")).Copy
Worksheets("main").Cells(cfind.Row, "H").PasteSpecial
End If

Next c

End With
nextj:
Next j


End Sub

Match data from multiple w/books & paste row

1 response

Similar discussions

Subscribe To Our Newsletter!