Split Every n Rows From Worksheet Into New Worksheets

[abhi221996]

I have a large worksheet. Over 40,000 rows. I want to split rows to new worksheets after every 5 times the list of -999.98 value is repeated in the column after any positive value for example given below.

 

[maabadi]

Welcome to MrExcel Message Board.
Please show result that you want also.

 

[abhi221996]

Thank you, and I am glad to part of this Forum.
I want to split the data of the masterSheet in a way that the result given in the splitted sheets by just one click,
after every times the - 999.98 value list appear the data is splitted after the first -999.98 value cell of the forth time appearance of the -999.98 list.
The result i want are displayed in the below pictures

 

[maabadi]

I supposed your data is in Column B to D (based sample data) and in First sheet. then use this macro:

Sub Test()
Dim Lr As Long, C As Long, F As Long, L As Long, K As Long, i As Long
With Sheets(1)
Lr = .Range("D" & Rows.Count).End(xlUp).Row
C = 1: F = 1: K = 2
For i = 2 To Lr
If .Range("D" & i).Value = -999.98 And .Range("D" & i - 1).Value <> -999.98 Then C = C + 1
If C = 4 Or i = Lr Then
If K > Sheets.Count Then Sheets.Add After:=Sheets(Sheets.Count)
.Range("B" & F & ":D" & i).Copy Sheets(K).Range("B1")
C = 1: F = i + 1: K = K + 1
End If
Next i
End With
End Sub

 

[abhi221996]

Thank you so much it means alot. It worked perfectly. You are True genius.
sorry i am new to vba.
If you could give a little explanation to the code .