How to Split my Data to Different sheet

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.

excel

HansVogelaar

Jun 01, 2022

thobenjo

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")
    Do
        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

HansVogelaar

MVP

Jun 01, 2022

thobenjo

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")
    Do
        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

thobenjo

Copper Contributor

Jun 02, 2022

Thank you so much !

Forum Discussion

How to Split my Data to Different sheet

Resources