Copy, Find Row, Then Paste Between Worksheets

[eririt]

I collect orders through e-mailed Order Form Workbooks (file names will vary). When the order arrives, my macro unprotects the Order Form, unhides the Order Summary tab, copies the order data and activates the Order List tab in the Master Workbook, which is already open in the background and is where the macro is saved.

From here, I manually search for the Store ID # in Column A and paste the Order Data into that store's row. I'd like to automate this part of it by teaching the macro to reference the Store ID # in Cell A3 of the Order Form Workbook and then look for that ID # in the Master Workbook and paste the data.

This is my current code and attached workbooks for examples. Thank you!

[COLOR=#222222][FONT=Verdana]Sub CopyPaste()[/FONT][/COLOR]
ActiveWorkbook.Unprotect "NAN"   'Order Workbook will arrive password protected
Sheets("OrderData").Visible = True         'Sheet with order data is hidden
Sheets("OrderData").Activate

'This copies Order data to make it ready for pasting into the Master workbook.

Dim storeOrder As Range
Set storeOrder = Range(Cells(3, 2), Cells(3, 52))
storeOrder.Copy

ActiveWorkbook.Activate       'To switch from Order Form Workbook to Master Workbook

'MasterOrders Sheet contains Store ID # list in range A3:A250.
Sheets("MasterOrders").Activate

How to I continue by finding the Store ID # in column A and then paste StoreOrder in the matching row?

 

[DILIPandey]

Hi eririt,

Since you already know coding, I would suggest you to search internet for VBA Loops and you will find the solution. Thanks.

Regards,
DILIPandey

 

[svendiamond]

copies the order data and activates the Order List tab in the Master Workbook, which is already open in the background and is where the macro is saved.

From here, I manually search for the Store ID # in Column A and paste the Order Data into that store's row. I'd like to automate this part of it by teaching the macro to reference the Store ID # in Cell A3 of the Order Form Workbook and then look for that ID # in the Master Workbook and paste the data.

Just want to make sure I'm getting this straight, your workbooks and sheets are:

Workbooks("Order Form").Sheets("OrderData")

and

Workbooks("Master").Sheets("MasterOrders")

Correct?

 

[svendiamond]

Save back-ups of your workbooks, then try something like this:

Sub myCopyPaste()

Dim storeOrder As Range, storeID As Variant, storeRow As Long

ActiveWorkbook.Unprotect "NAN"

With Sheets("OrderData")
    .Visible = True
    storeID = .Range("A3").Value
    .Range(.Cells(3, 2), .Cells(3, 52)).Copy
End With

With Workbooks("Master").Sheets("MasterOrders")
    storeRow = .Range("A:A").Find(storeID).Row
        .Range("B" & storeRow).PasteSpecial
End With

End Sub

 

[eririt]

Almost. The "Order Form" workbook actual file name will vary. This is the workbook that gets e-mailed back to me from 200+ different contacts. "OrderData" is the sheet name and will always have that name. ("Master").Sheets("MasterOrders") is going to be consistent as this is the file I own and compile all orders into.

 

[svendiamond]

Almost. The "Order Form" workbook actual file name will vary. This is the workbook that gets e-mailed back to me from 200+ different contacts. "OrderData" is the sheet name and will always have that name. ("Master").Sheets("MasterOrders") is going to be consistent as this is the file I own and compile all orders into.

Ok, good. See if the code I posted works.