Copying Data between Spreadsheets based on a rows value

[Phil Gold]

Hi there,

Looking for a bit of assistance in Excel with VB macro.
I have 2 spreadsheets. One has an order number and a few cells of data. The second has a column of order numbers and each row has data regarding that order. I am looking for code that will run when an on screen button is pressed that grabs the order number and data from the first spreadsheet and then in the new spreadsheet look for the row which has the same order number (In say column A) and paste the data in another column in that row (Say column M).

EG.
Spreadsheet 1
Order Number Data1 Data2 Data3
5468 #new# #new# #new#


Spreadsheet 2
Order Number DataA DataB Data1 Data2 Data3
2245
6315
5468 existing data #new# #new# #new#

Any help is much appreciated.

Regards

Phil



Submitted via EggHeadCafe - Software Developer Portal of Choice
Yet Another Service Wrapper: The Ultimate Service
http://www.eggheadcafe.com/tutorial...9-b9b6f957b9e6/yet-another-service-wrapp.aspx

 

[marcus]

Hi Phil

The following procedure does what you want but you will have to change
the variables (workbook name, cell the data starts in). It assumes
you place the cursor in the cell where the Order number in Sheet 1.
If the order number is in a specific cell you may change to suit. It
also assumes you are only copying 7 columns of data in line with your
example.

Anyways I hope it helps.

Take care

Marcus


Option Explicit
Sub MovetoNew()
Dim N As Integer
Dim WB1 As Workbook
Dim WB2 As Workbook
Dim i As Integer
Dim MyFind As String

Set WB1 = Workbooks("StBk.xls") 'change to suit
Set WB2 = Workbooks("EndBk.xls") 'change to suit
MyFind = ActiveCell.Value

If MyFind = "" Then
MsgBox "Place cursor in the Order field."
Exit Sub
End If

Range(Cells(2, 1), Cells(2, 7)).Copy
WB2.Sheets("sheet1").Activate
i = Cells.Find(MyFind, LookIn:=xlValues).Row
WB2.Sheets("Sheet1").Cells(i, 1).PasteSpecial xlPasteValues

End Sub