How to Split my Data to Different sheet

New Contributor


Experts please see this report , please I need to split the data to different sheets, like data after blank to one sheet then the next data to another  etc

How do I do it.

2 Replies
best response confirmed by thobenjo (New Contributor)


Here is a macro you can run.

Sub Split2Sheets()
    Dim ws As Worksheet
    Dim wt As Worksheet
    Dim rg As Range
    Application.ScreenUpdating = False
    Set ws = ActiveSheet
    Set rg = ws.Range("A1")
        Set wt = Worksheets.Add(After:=Worksheets(Worksheets.Count))
        wt.Name = CStr(rg.Offset(0, 1).Value)
        rg.CurrentRegion.Copy Destination:=wt.Range("A1")
        Set rg = rg.End(xlDown).End(xlDown)
    Loop Until rg.Row = ws.Rows.Count
    Application.ScreenUpdating = True
End Sub
Thank you so much !