Split master sheet data to multiple sheets in same workbook

**Apple Ling** · 09-26-2012, 04:25 AM

Hi,

I have to copy every 50th rows data in the master file to different sheet in the same workbook. For example, 1st 50th row into sheet1, 51th to 100th row to sheet2, 101th-150th data to sheet3 etc.

I'm not a VBA expert and the following hit syntax errors.

I hope someone can help. Thank you very much.

Sub SeparateData()
Dim rLastCell As Range
Dim DataSH As Worksheet
Dim WrkSH As Worksheet
Dim lLoop, lCopy, a As Long


Application.ScreenUpdating = False

Set DataSH = Sheets("ExportData")


With ThisWorkbook.Sheets("ExportData")
    Set rLastCell = .Cells.Find(What:="*", After:=[a1], SearchDirection:=xlPrevious)

    For lLoop = 1 To rLastCell.Row Step 50
        lCopy = lCopy + 1
        Set WrkSH = Sheets.Add  'create worksheet
        For a = 1 To Sheets.Count
        DataSH.Range(.Cells(lLoop, 1), .Cells(lLoop + 50, .Columns.Count)).EntireRow.Copy _
        Destinations:=WrkSH.Sheets(a).Range("A2")
        Next a
    Next lLoop
End With

Application.ScreenUpdating = True

End Sub

**mike7952** · 09-26-2012, 04:45 AM

Give this a try

Sub SeparateData()
Dim rLastCell As Long
Dim DataSH As Worksheet
Dim lLoop, lCopy, a As Long


Application.ScreenUpdating = False

Set DataSH = Sheets("ExportData")

With DataSH
    rLastCell = .Cells.Find(What:="*", After:=[a1], SearchDirection:=xlPrevious).Row
    For lLoop = 1 To rLastCell Step 50
        Worksheets.Add After:=Worksheets(Worksheets.Count)  'create worksheet
        .Range(.Cells(lLoop, 1), .Cells(lLoop + 49, 1)).EntireRow.Copy Range("A2")
    Next lLoop
End With

Application.ScreenUpdating = True

End Sub