SOLVED

How to Split my Data to Different sheet

Copper Contributor

Sample.PNG

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 (Copper Contributor)
Solution

@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
Thank you so much !
1 best response

Accepted Solutions
best response confirmed by thobenjo (Copper Contributor)
Solution

@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

View solution in original post