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
![Frown :( :(](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f641.png)
![23js3n9.png](/board/proxy.php?image=http%3A%2F%2Fi67.tinypic.com%2F23js3n9.png&hash=aeb49ce2b2b333e8e8f698a685ed6cfc)
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 !