Find and Paste

[Guest]

I am trying to copy a range (B1..B12) from Sheet1 to a row on Sheet2. The
row to paste to is based upon the value in Sheet1 (A1) which is 10.

So far I have;

Sheets("Sheet1").Select
If Range("D15") <> Range("D17") Then
Range("b1..b12").Select
Selection.Copy
Sheets("sheet2").Select

At this point I need for it to find which row in column (A) has 10 in it and
then go over 3 columns to C and paste.

Thanks a lot for any help.

 

[Guest]

Give this a try...

Dim rngFound As Range

Set rngFound = Sheets("Sheet2").Columns("A").Find(What:=10, _
LookAt:=xlWhole, _
LookIn:=xlValues)
If rngFound Is Nothing Then
MsgBox "Sorry. Not found..."
Else
With Sheets("Sheet1")
If .Range("D15") <> .Range("D17") Then _
.Range("B1:B12").Copy rngFound.Offset(0, 3)
End With
End If

 

[Guest]

Thanks Jim for the help. Its exactly what I am looking for. For some reason
it was putting the data in row 11, but by changing the offset to -1 it puts
it in the correct row.

Also, I need to change the What:=10 to What:=Sheet1!A1. What is the correct
syntax for such?

 

[Guest]

Also, how can I get it to paste values? It is pasting formulas.
again Thanks

 

[Guest]

Dim rngFound As Range

Set rngFound =
Sheets("Sheet2").Columns("A").Find(What:=Sheets("Sheet1").Range("A1").Value, _
LookAt:=xlWhole, _
LookIn:=xlValues)
If rngFound Is Nothing Then
MsgBox "Sorry. Not found..."
Else
With Sheets("Sheet1")
If .Range("D15") <> .Range("D17") Then _
.Range("B1:B12").Copy
rngFound.Offset(0, 3).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End With
End If

 

[Guest]

Thanks A LOT for your time and expertise. Its PERFECT.