Copy cells that vary in range

[Guest]

Hi everyone!

I need some assistence to come up with come code that will copy a range of
cells from a single column. The text in the first cell and the last cell are
always the same. But sometimes there could be 5 cells inbetween and other
times there could be 100 cells inbetween. Below is what I ahve come up with
to so far to show where I am trying to go. Thank you for your help!!

startrow = Columns(2).Find("start example 1").Select
ActiveCell.Copy

******Need to also copy all cells inbetween here****

startrow = Columns(2).Find("end example 1").Select
ActiveCell.Copy

 

[Guest]

Hi,

here's one way :


Sub test()
Dim rStart As Range
Dim rEnd As Range

Set rStart = Columns(2).Find("start example 1")

Set rEnd = Columns(2).Find("end example 1")

If Not rStart Is Nothing And Not rEnd Is Nothing Then
Range(rStart, rEnd).Copy
End If
End Sub

 

[Guest]

Rock on!! thank you!!!!!

Hi,

here's one way :


Sub test()
Dim rStart As Range
Dim rEnd As Range

Set rStart = Columns(2).Find("start example 1")

Set rEnd = Columns(2).Find("end example 1")

If Not rStart Is Nothing And Not rEnd Is Nothing Then
Range(rStart, rEnd).Copy
End If
End Sub

 

[Guest]

hi
try something like this.....

Sub Macro1()
Dim f As Range
Dim l As Range
Set ws = Sheets("Sheet1")
With ws.Range("A1:A10000")
Set f = .Find("firstfind", LookIn:=xlValues)
Set l = .Find("lastfind", LookIn:=xlValues)
Range(f, l).Select
End With
End Sub
edit for your data

Regards
FSt1

 

[gordon.moar]

I need some assistence to come up with come code that will copy a range of
cells from a single column. The text in the first cell and the last cell are
always the same. But sometimes there could be 5 cells inbetween and other
times there could be 100 cells inbetween. Below is what I ahve come up with
to so far to show where I am trying to go. Thank you for your help!!

startrow = Columns(2).Find("start example 1").Select
ActiveCell.Copy

******Need to also copy all cells inbetween here****

startrow = Columns(2).Find("end example 1").Select
ActiveCell.Copy

How will the range be populated? If it is by the insertion of rows
then you could name the range in the worksheet and just copy it,
regardless of how many rows are added.

Alternatively, do something like this:

Dim rngCopyRange As Range
Dim lngStartRow As Long
Dim lngEndRow As Long
lngStartRow = Columns(2).Find("start example 1").Row
lngEndRow = Columns(2).Find("end example 1").Row
Set rngCopyRange = Range("B" & lngStartRow & ":B" & lngEndRow)
rngCopyRange.Copy

 

[gordon.moar]

Dim rngCopyRange As Range
Dim lngStartRow As Long
Dim lngEndRow As Long
lngStartRow = Columns(2).Find("start example 1").Row
lngEndRow = Columns(2).Find("end example 1").Row
Set rngCopyRange = Range("B" & lngStartRow & ":B" & lngEndRow)
rngCopyRange.Copy

...although Vergel's way is more elegant!

 

[Guest]

I forgot to ask about my next step which is pasting that range into another
worksheet starting at cell "a10" in the new worksheet. Thank you!

 

[Guest]

try replacing this line

Range(rStart, rEnd).Copy

with this one

Range(rStart, rEnd).Copy ThisWorkbook.Worksheets.Add.Range("A10")


... if the destination is on a sheet that already exists, for example, a
sheet named "Sheet2", then, you can do it this way:

Range(rStart, rEnd).Copy ThisWorkbook.Worksheets("Sheet2").Range("A10")

 

[Guest]

Thank you!!

try replacing this line

Range(rStart, rEnd).Copy

with this one

Range(rStart, rEnd).Copy ThisWorkbook.Worksheets.Add.Range("A10")


.. if the destination is on a sheet that already exists, for example, a
sheet named "Sheet2", then, you can do it this way:

Range(rStart, rEnd).Copy ThisWorkbook.Worksheets("Sheet2").Range("A10")