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



--
Regards
Dave Hawley
www.ozgrid.com
"Stuart" wrote in message
...
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.

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

On Apr 22, 11:29*am, "ozgrid.com" wrote:
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

--
Regards
Dave Hawleywww.ozgrid.com"Stuart" wrote in message

...



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.

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" wrote:

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

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


On Apr 22, 12:14*pm, Mike H wrote:
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" wrote:
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.
.

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

On Apr 23, 9:07*pm, Stuart wrote:
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

On Apr 22, 12:14*pm, Mike H wrote:



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" wrote:
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.
.