Macro to copy and paste all rows of data in between two words

[Stuart]

Dear Group

This is probably somewhere here on this site but I cannot seem to
track it down so I hope someone can help me.

I am looking for a macro that can find the word "Start" in column B
and then move one row and select all the rows until the word "End" is
found in Column B".

Then copy from sheet named "Datafrom" to sheet named "Datato".

I really hope someone can help with this.

Thanks very much in advance.

 

[ozgrid.com]

Try;

Sub DoIt()
Dim rRange As Range

On Error Resume Next
Set rRange = Range(Range("A:A").Find("Start", _
Cells(1, 1), xlFormulas, _
xlPart, xlByRows, xlNext, False), _
Range("A:A").Find("End", _
Cells(1, 1), xlFormulas, _
xlPart, xlByRows, xlNext, False))
On Error GoTo 0

If Not rRange Is Nothing Then
Application.Goto rRange.EntireRow
Else
MsgBox "'Start' or 'End' not found"
End If
End Sub

 

[Stuart]

Dave

Thanks for this.

I made a slight change to the code as follows so that it would look up
column B.

Sub DoIt()
 Dim rRange As Range

 On Error Resume Next
 Set rRange = Range(Range("B:B").Find("Start", _
        Cells(1, 2), xlFormulas, _
            xlPart, xlByRows, xlNext, False), _
            Range("B:B").Find("End", _
        Cells(1, 2), xlFormulas, _
            xlPart, xlByRows, xlNext, False))
On Error GoTo 0

 If Not rRange Is Nothing Then
  Application.Goto rRange.EntireRow
 Else
   MsgBox "'Start' or 'End' not found"
 End If
End Sub

Is there a way to add a forumla automatically in column I for all the
rows selected above. The formula would be ="DATA"&" - "&B8&" - "&C8&"
- "&E8&"EUR".

Also your macro above does not copy all the above selected rows and
paste them into another worksheet called "Datato".

Can this be added?

Kind Regards,

Stuart

 

[Mike H]

Stuart,

try this

Sub CopyData()
Set srcSht = Sheets("Datafrom")
Set dstSht = Sheets("Datato")
Dim lastrow As Long
Dim StRow As Range
Dim EndRow As Range
Set StRow = srcSht.Range("B:B").Find("Start", LookIn:=xlValues,
lookat:=xlWhole)
Set EndRow = srcSht.Range("B:B").Find("End", LookIn:=xlValues,
lookat:=xlWhole)
If StRow Is Nothing Or EndRow Is Nothing Then
MsgBox "Range Not Found"
Exit Sub
End If
lastrow = dstSht.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
srcSht.Rows(StRow.Row & ":" & EndRow.Row).Copy
Destination:=dstSht.Range("A" & lastrow)
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Stuart]

Mike

Thanks for your reply.

Do you happen to know how I can add the following formula into the
last column of data?

="DATA"&" - "&B8&" - "&C8&"

I want to add the above formula into the last column where there are
rows containing text in the first cell of that row.

I appreciate that this is a rather complicated request but I was
hoping that someone on the forum would be able to assist.

Kind Regards,

Stuart

 

[Stuart]

Hi Mike

Do you know what I should modify in your code to paste the data to the
new sheet as values only?

Kind Regards,

Stuart