copy data where exact match

  • Thread starter Thread starter djssuk
  • Start date Start date
D

djssuk

I have two spreadsheets.

Spreadsheet 1 has reference numbers R1 to R100 in column b and goin
down the rows.
Spreadhseet 2 has reference numbers R1 to R100 in column b and goin
down the rows.

Colums b-h contains the same column names in both spreadhseets bu
different data.

I want to be able to click on a button on spreadsheet one next to on
of the rows (one button for each row and therefore, each reference) an
for it to load spreadsheet two and for the button to find an exac
match on spreadsheet two and copy the data from spreadhseet one on t
spreadhseet two, overt the existing data for the match.

For instance, if row one had a button, I would click on the button an
if the reference in the column A is R1, the button would look fo
reference R1 on spreadhseet two and copy the contents from column B t
H from spreadhseet one over the data in spreadsheet two for referenc
R1, columns B to H.

Anyone have any VBA code I could use please... please help..
 
This may help you out, you will need only one button on sheet 1 and you will
need to leave cell a1 in sheet 2 available ( you can adjust to suit and use
a hidden cell ), you can also set the macro with a short cut key so you do
not need any buttons at all.

edit sheet names to suit ( these are just 1 and 2 in the example )

to work it simply click on the cell in col b you wish to transfer and run
the macro, there may be better ways but it should get you started.

R
Pete


Sub COPYME()
If ActiveCell.Column <> 2 Or ActiveCell.Row > 100 Then
MsgBox " Please select the data in column B rows 1 - 100"
Exit Sub
End If
Application.ScreenUpdating = False
Sheets("2").Range("A1").Value = ActiveCell.Row - 1
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
Selection.Resize(numRows + 0, numColumns + 6).Select
Selection.Copy
Sheets("2").Select
Range("B1").Select
ActiveCell.Offset(Range("A1").Value, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Sheets("1").Select
Range("A1").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
 
Thank you very much Pete, I will try it later today.

I am very grateful for your response either way.

Thanks again
 
Back
Top