How to copy blocks or chunks of data from a master worksheet to new worksheets automatically with Excel VBA. Watch the video below:

We may need to copy a number of rows or columns from a master sheet to a new sheet or to multiple sheets. Doing this manually will waste time. Given below is the macro VBA code to automate the process. You can also modify the process slightly by first counting the number of sheets already available in the workbook by using this one line of macro code:

n = Worksheets.Count

Sub CopyTenRowsToNewSheet()
Dim lastrow, lastcolumn As Long

lastrow = Application.WorksheetFunction.CountA(Range("A:A"))
lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

Dim i, m, n As Long
n = Worksheets.Count
m = 9

For i = 1 To lastrow Step 10
    
    Sheet1.Range(Cells(i, 1), Cells(i + m, lastcolumn)).Copy
    Worksheets.Add after:=Sheets(n), Count:=1
    n = n + 1
    ActiveSheet.Paste
    Sheet1.Activate
    
Next i

End Sub