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

  • Thread starter Thread starter Stuart
  • Start date Start date
S

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.
 
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
 
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
Click to expand...

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

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
 
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
 
Back
Top