Excel VBA copy paste subsequent rows

[Raptor776]

Hello,

Say I have the following table, where whenever a value between certain numbers appear it copies and pastes the subsequent five rows including itself, totaling 6 rows copied. For example, if column c has a value between 0.3 to 0.4. It would copy and paste the next 5 rows to a different spreadsheet.

Name	Location	Value
ABC	California	0.2
DEF	New York	0.36
GHI	Florida	0.1
JKL	Texas	0.6
MNO	Ohio	0.29
PQR	Arizona	0.84
STU	Nebraska	0.38

<tbody>
</tbody>

This is a small sample size, however I would want it to be continuous so if when it sees 0.38 in the last row it would repeat the copy paste progress of the next 5 rows including itself. And this will essentially populate the subsequent sheet.

Thank you very much!

 

[jim may]

First Copy your Header from Sheet1 to Sheet2 (Range(A1:C1))
then from Sheet1 run this Macro

Sub Doit()
Dim Rng As Range
Application.ScreenUpdating = False
Range("C2").Select
r = 2
Do Until ActiveCell = ""
If ActiveCell.Value >= 0.3 And ActiveCell <= 0.4 Then
Set Rng = Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row + 5, 3))
    Rng.Copy _
    Worksheets("Sheet2").Cells(r, 1)
r = r + 6
ActiveCell.Offset(1).Select
Else
ActiveCell.Offset(1).Select
End If
Loop
Application.ScreenUpdating = True
End Sub

 

[Raptor776]

Works amazing Jim, thank you very much!!!

Had been driving me crazy trying to figure it out.