Splitting datasets by their titles into multiple sheets

Try this.

Sub Test()
Sheets("All").Activate
For N = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    If Cells(N, 1) <> "" And Cells(N - 1, 1) = "" Then
        Venue = Cells(N, 1)
        On Error GoTo NewVenue
        Cells(N, 1).CurrentRegion.Copy Destination:=Sheets(Venue).Cells(Rows.Count, 1).End(xlUp).Offset(2, 0)
        On Error GoTo 0
        Sheets(Venue).Columns.AutoFit
    End If
Next N
Exit Sub

NewVenue:
Sheets.Add After:=Sheets("All")
ActiveSheet.Name = Venue
Sheets("All").Activate
Resume

End Sub

That is insane! Just wondering if you can breakdown the methadology for creating this macro, as i would be glad of the ability to make my own to this level. If you dont have the time, ill just say thanks!

Your reputation has been amended as such!

Cheers Alan

OK.

The code is going down your All sheet and looking for the tops of blocks of filled cells by looking where a cell is filled and the one above it is empty.

It then tries to copy the block to the appropriate sheet. If the sheet does not yet exist, the error code the the bottom is invoked to create the sheet and the code returns to where it left off.

A good way to see what is happening is to run in debug mode. Get the VBA window small with the sheet maximised in the background. With the cursor somewhere in the code block, continually press F8 to advance the code one line at a time. You can watch the effect on the main sheet.

Hope this helps.

First of all thanks so much for the help so far,

Ok whilst i can get this working on the example spreadsheet for some reason when i apply this to a spreadsheet in th same format as my real spreadsheet you will notice that it pulls the entire sheet as oppose to just those datasets which have the same name? It pulls the entire sheet the same amount of times it encounters the tab name.

For instance it finds "Ashford" twice and so pulls the "all" sheet a first time, offsets by 4 and pulls the entire "All" sheet again. Any idea how to overcome this? Is it a result of the conditional formatting? (I cannot do without the formating)

Any help would be greatly appreciated.

Many thanks

Alan

You appear to have something in the rows between the blocks of data on the All sheet which is making it appear as one huge block.

If select those rows (16,17,26, 27 etc) and then hit the delete key, the macro start to work fine.

The following alternative deduces the range to copy in a different way by looking at cell values in column E and may therefore be more robust.

Sub Test()

Sheets("All").Activate
For N = 2 To Cells(Rows.Count, 5).End(xlUp).Row
    If Cells(N, 5) <> "" And Cells(N - 1, 5) = "" Then
        Venue = Cells(N, 5)
        On Error GoTo NewVenue
        For M = N To Cells(Rows.Count, 5).End(xlUp).Row
            If Cells(M, 5) = "" Then Exit For
            LastRow = M
        Next M
        Set CopyRange = Range(Cells(N, 5), Cells(LastRow, Cells(N + 1, Columns.Count).End(xlToLeft).Column))
        CopyRange.Copy Destination:=Sheets(Venue).Cells(Rows.Count, 5).End(xlUp).Offset(2, 0)
        On Error GoTo 0
        Sheets(Venue).Columns.AutoFit
    End If
Next N
Exit Sub

NewVenue:
Sheets.Add After:=Sheets("All")
ActiveSheet.Name = Venue
Sheets("All").Activate
Resume

End Sub

Hello Mrice.I checked what you said in that if i delete rows 16,17,26, 27 then the macor would work, indeed it does, how strange since i can find no data in those colomns. However the newer more robust macro works a dream! Many many thanks for your help! I will mark this thread as solved but if i run into any further limits of the macro, i shall of course reopen it!

Your reputation has been ammended as a result of your help!

Thanks Again Alan

Hello Mrice, after an update to one of my systems, the data comes out in a slightly different format to before. It is essentially the same task but spatially separated differently as well as containing conditional formatting. Im hoping that the conditional formatting can follow the data across to the new tabs.

Please see updated example.

Many thanks

Alan

Please try the code below.

Option Explicit

Sub SplitUpByLocation()
Dim Sheet As Worksheet
Dim FoundLocationSheet As Boolean
Dim N As Long
Dim AllSheet As Worksheet

Application.DisplayAlerts = False
For Each Sheet In ActiveWorkbook.Sheets
    If Sheet.Name <> "All" Then
        Sheet.Delete
    End If
Next Sheet
Set AllSheet = ActiveSheet
Application.DisplayAlerts = True

'Determine list of locations
For N = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    If AllSheet.Cells(N, 1) = "Staff" Then
        FoundLocationSheet = False
        For Each Sheet In ActiveWorkbook.Sheets
            If Sheet.Name = AllSheet.Cells(N - 1, 1) Then
                FoundLocationSheet = True
                Exit For
            End If
        Next Sheet
        If FoundLocationSheet = False Then
            ActiveWorkbook.Sheets.Add After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
            ActiveSheet.Name = AllSheet.Cells(N - 1, 1)
        End If
        
    End If
Next N

'Transfer data
AllSheet.Activate

For N = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    If AllSheet.Cells(N, 1) = "Staff" Then
        AllSheet.Cells(N, 1).CurrentRegion.Copy Destination:=Sheets(AllSheet.Cells(N - 1, 1).Value).Cells(Rows.Count, 1).End(xlUp).Offset(2, 0)
    End If
Next N

For Each Sheet In ActiveWorkbook.Sheets
    Sheet.Columns.AutoFit
Next Sheet

End Sub

Hi Martin, many thanks for your macro. It works in that it creates new sheets with correct sheet names and imports the data correctly. However the original sheet has a complex list of conditional formats. These conditional formats have a particular order to create the aesthetics for the final sheet, when the new sheets are created it distorts the order of these formats and the produced sheet doesn't look anywhere near like it is supposed to. Is it possible to apply/duplicate conditional formatting via macro? I guess another alternative would be to copy the original sheet, "All" in this case, clear it, rename it and duplicate the locational data as before, therefore maintaining the original conditional formatting?

It is possible to apply conditional formatting by macro. A good way to get an idea of the syntax is to record some being applied and inspect what you get.