Splitting Active Worksheet into multiple sheets

[DV07]

Hey Guys, I am trying to split a master worksheet which looks like this:

I have attached a sample file onto this thread.

I am basically trying to open a new sheet at every instance of the break in the current sheet, being the --------

How would I go about doing this in VBA?

Many thanks

D

[JLGWhiz]

You can upload your actual file or a mock-up by clicking 'Go Advanced' at the bottom of the reply box, then click 'Manage Attachments' and follow the menu. Then we can work with your actual data to provide a solution.

[DV07]

Thanks for your reply

I have attached a sample file onto this thread

Kind Regards

[JLGWhiz]

If you add a dashed line beneath the last group of data then this should do what you want.

Please Login or Register  to view this content.

[Marc L]

A beginner starter demonstration according to the attachment as it is (no needs any mod) :

Sub Demo1() 
           Dim Rg As Range, F&, R& 
    With Sheet1.UsedRange.Rows 
           Set Rg = .Columns(1).Find("-*-", , xlValues, xlWhole) 
        If Not Rg Is Nothing Then 
                F = 1 
                R = Rg.Row 
            Do 
                Sheets.Add , Sheets(Sheets.Count) 
               .Item(F & ":" & Rg(0).Row).Copy [A1] 
                   F = Rg(2).Row 
                   Set Rg = .Columns(1).FindNext(Rg) 
            Loop Until Rg.Row = R 
                   Set Rg = Nothing 
            If F <= .Count Then 
                Sheets.Add , Sheets(Sheets.Count) 
               .Item(F & ":" & .Count).Copy [A1] 
            End If 
        End If 
    End With 
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[DV07]

Thank you so much guys!