return cell content using fixed terms to find range

[tintin1012000]

I have a complex one
In Sheet 1 I have a dynamic table of info which changes daily
I have 2 fixed terms '' FINISHED_GOOD and downtimeparetoTable which are always in Column A but can move rows, it depends on how many products I produce, sometimes it can be 3 product other days it might be 20

I want to use these terms to return the values between them and display in Sheet 2

so sheet 2 would show me the products build, I can use a Vlookup to get the Qty's from Column B

Example of Sheet 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[/TR]
[TR]
[TD]

<colgroup><col width="289"></colgroup><tbody>
[TD="width: 289"]FINISHED_GOOD
[/TD]

</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product 1
[/TD]
[TD]564
[/TD]
[/TR]
[TR]
[TD]Product 2
[/TD]
[TD]123
[/TD]
[/TR]
[TR]
[TD]Product 3
[/TD]
[TD]345
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]downtimeparetoTable
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[KolGuyXcel]

Try this!

Sub mid_rows()
Dim f_row As Long, l_row As Long, i As Long
f_row = Range("A:A").Find("FINISHED_GOOD").Row
l_row = Range("A:A").Find("downtimeparetoTable").Row
For i = f_row + 1 To l_row - 1
Sheets("Sheet4").Cells(i - f_row + 1, 1) = Cells(i, 1)
Next
End Sub

 

[tintin1012000]

Thanks, this worked
is it possible to adjust slightly to paste results starting in Cell A17 of sheet 4 ?

thanks a million

Try this!

Sub mid_rows()
Dim f_row As Long, l_row As Long, i As Long
f_row = Range("A:A").Find("FINISHED_GOOD").Row
l_row = Range("A:A").Find("downtimeparetoTable").Row
For i = f_row + 1 To l_row - 1
Sheets("Sheet4").Cells(i - f_row + 1, 1) = Cells(i, 1)
Next
End Sub

 

[KolGuyXcel]

This should do.

Sub mid_rows()
Dim f_row As Long, l_row As Long, i As Long
f_row = Range("A:A").Find("FINISHED_GOOD").Row
l_row = Range("A:A").Find("downtimeparetoTable").Row
For i = f_row + 1 To l_row - 1
Sheets("Sheet4").Cells(i - f_row + 16, 1) = Cells(i, 1)
Next
End Sub

 

[tintin1012000]

Thanks
that works perfect, lifesaver !

This should do.

Sub mid_rows()
Dim f_row As Long, l_row As Long, i As Long
f_row = Range("A:A").Find("FINISHED_GOOD").Row
l_row = Range("A:A").Find("downtimeparetoTable").Row
For i = f_row + 1 To l_row - 1
Sheets("Sheet4").Cells(i - f_row + 16, 1) = Cells(i, 1)
Next
End Sub

 

[KolGuyXcel]

You're welcome!

Happy to help!