Tách dữ liệu trong một trang tính theo số hàng cụ thể bằng VBA trong Microsoft Excel

Option Explicit

Sub SplitDataToMultipleSheets()

'Declaring variables

Dim LastRow As Long, n As Long, CntRows As Long

Dim LastColumn As Integer

'Getting count of number of rows required in one sheet

CntRows = CInt(Sheets("Main").TextBox1.Value)

'Disabling screen updates

Application.ScreenUpdating = False

With Sheets("RawData")



'Getting row number and column number of last cell

LastRow = .Range("A" & .Rows.Count).End(xlUp).Row

LastColumn = .Range("A1").SpecialCells(xlCellTypeLastCell).Column



'Looping through data in the sheet

For n = 1 To LastRow Step CntRows



'Adding new worksheet

Sheets.Add after:=Sheets(Sheets.Count)

'Copying data to new worksheet

.Range("A" & n).Resize(CntRows, LastColumn).Copy Range("A1")

Next n



.Activate

End With

'Enabling screen updates

Application.ScreenUpdating = True

End Sub