SOLVED

How to Split my Data to Different sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-3453940%22%20slang%3D%22en-US%22%3EHow%20to%20Split%20my%20Data%20to%20Different%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3453940%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Sample.PNG%22%20style%3D%22width%3A%20664px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Sample.PNG%22%20style%3D%22width%3A%20664px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Sample.PNG%22%20style%3D%22width%3A%20664px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Sample.PNG%22%20style%3D%22width%3A%20664px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Sample.PNG%22%20style%3D%22width%3A%20664px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Sample.PNG%22%20style%3D%22width%3A%20664px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Sample.PNG%22%20style%3D%22width%3A%20664px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F376652iE2C72969E4A451C1%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Sample.PNG%22%20alt%3D%22Sample.PNG%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EExperts%20please%20see%20this%20report%20%2C%20please%20I%20need%20to%20split%20the%20data%20to%20different%20sheets%2C%20like%20data%20after%20blank%20to%20one%20sheet%20then%20the%20next%20data%20to%20another%26nbsp%3B%20etc%3C%2FP%3E%3CP%3EHow%20do%20I%20do%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3453940%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3454228%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20Split%20my%20Data%20to%20Different%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3454228%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1409719%22%20target%3D%22_blank%22%3E%40thobenjo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20a%20macro%20you%20can%20run.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20Split2Sheets()%0A%20%20%20%20Dim%20ws%20As%20Worksheet%0A%20%20%20%20Dim%20wt%20As%20Worksheet%0A%20%20%20%20Dim%20rg%20As%20Range%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20Set%20ws%20%3D%20ActiveSheet%0A%20%20%20%20Set%20rg%20%3D%20ws.Range(%22A1%22)%0A%20%20%20%20Do%0A%20%20%20%20%20%20%20%20Set%20wt%20%3D%20Worksheets.Add(After%3A%3DWorksheets(Worksheets.Count))%0A%20%20%20%20%20%20%20%20wt.Name%20%3D%20CStr(rg.Offset(0%2C%201).Value)%0A%20%20%20%20%20%20%20%20rg.CurrentRegion.Copy%20Destination%3A%3Dwt.Range(%22A1%22)%0A%20%20%20%20%20%20%20%20Set%20rg%20%3D%20rg.End(xlDown).End(xlDown)%0A%20%20%20%20Loop%20Until%20rg.Row%20%3D%20ws.Rows.Count%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3458585%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20Split%20my%20Data%20to%20Different%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3458585%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much%20!%3C%2FLINGO-BODY%3E
New 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 (New 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 !