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

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.
 
O

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
 
S

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
 
M

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.
 
S

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
 
S

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top