November 10, 2021

Copy Blocks of Data From Master Sheet to New Sheets with VBA

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

Copy chunks of Data From Master Sheet to New Sheets automatically

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



Copy Blocks of Data From Master Sheet to New Sheets with a macro

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.