VBA need help with COPY/Find/paste

cazdealer

Board Regular
Joined
Mar 12, 2011
Messages
96
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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
Something like this? (untested)

Code:
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

Board Regular
Joined
Mar 12, 2011
Messages
96
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

Board Regular
Joined
Mar 12, 2011
Messages
96
I just removed the "Exit Sub" and it's working like a charm.
thanks again Neil!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,484
Messages
5,596,407
Members
414,064
Latest member
Duncthegreat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top