jinkame123
New Member
- Joined
- Mar 31, 2018
- Messages
- 3
Dear all,
I intended to split a large excel table (~ 7,000 rows) into different sheets.
Take the following image as an example,
I wish to copy the rows from 2 (marked with an "s" in column B) to 3 (row above "E" in column B) into a separate sheet named under the "action list" column (in this case "2").
My codes works in the first round but failed to loop down to the end of the table
. Just wondering if the experts around would have idea on the case ?
Any suggestions would be appreciated, thanks in advance !
I intended to split a large excel table (~ 7,000 rows) into different sheets.
Take the following image as an example,
I wish to copy the rows from 2 (marked with an "s" in column B) to 3 (row above "E" in column B) into a separate sheet named under the "action list" column (in this case "2").
My codes works in the first round but failed to loop down to the end of the table


Code:
Sub test()
' cut rows according to string
Dim LastRow As Integer, i As Integer, AL As Long, s As Integer, e As Integer
LastRow = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To 164
If Range("B" & i).Value Like "S" Then
Range("C" & i).Value = AL
i = s
ElseIf Range("B" & i).Value Like "End" Then
e = i - 1
ThisWorkbook.Sheets.Add(After:=Sheets(ThisWorkbook.Sheets.Count)).Name = AL
Sheets(1).Rows(1).Copy Sheets(ThisWorkbook.Sheets.Count).Range("A1")
Sheets(1).Rows(s & ":" & e).Copy Sheets(ThisWorkbook.Sheets.Count).Range("A2")
End If
Next
End Sub
Any suggestions would be appreciated, thanks in advance !