VBA need help with COPY/Find/paste

[cazdealer]

Hi,
this is probably pretty basic to a lot of veteran VBA user but I couldnt find the answer after few hours trying to "google" it.
I would like to use this in a more complicated VBA procedure that have a loop.

I want to copy a range (A1:B1000) from sheet1 into sheet2.
I want this to be copied in sheet2 in the first row where value in column B is the same value of B1 (from sheet 1).

I cannot figure out how to select the cell in sheet 2 that represent the exact value of Cell(B1) in sheet 1.

thanks for the help

 

[njimack]

Something like this? (untested)

Sub Copy_Paste()
Dim r As Range
Dim x As Variant


With Sheets("Sheet1")
    x = .Range("B1").Value
    .Range("A1:B1000").Copy
End With

With Sheets("Sheet2")
    For Each r In Range("B1:B1000")
        If r.Value = x Then
            r.Offset(, -1).Paste
            Exit Sub
        End If
    Next r
End With


End Sub

 

[cazdealer]

Thanks Neil, this is working if I switch the little typo...
from offset (,-1) to offset (0,-1)

only a problem... I had already a loop in the Sub I wanted to put this in. I don't know if it's the exact term (loop). At the end, I got a next i.

anyways, now that's I've included this in my bigger sub, it seems that the sub stops there (I guess because it's looping for the next r).

How could it continues further?

thanks

 

[cazdealer]

I just removed the "Exit Sub" and it's working like a charm.
thanks again Neil!