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.
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
I supposed your data is in Column B to D (based sample data) and in First sheet. then use this macro:
Dim Lr As Long, C As Long, F As Long, L As Long, K As Long, i As Long
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