VBA code to split worksheet with multiple sheets

[shell2133]

Hello everyone. I would like to split a worksheet which has two sheets called "Position and "Assignment" at each change in column E, "Org L5", on both tabs, so create a new sheet with a position and assignment tab for each of the different Org L5s, preserving the headers and formulas etc. The headers on the Position tab are in A1:Y3, on the Assignment tab they're in A1:U1. I want to name the new sheets as per column E, the Org L5 name. I've done similar for one tab before but not two and I'm also struggling with the header on the first tab with it being in multiple rows. Would anyone know a code for this please? Thanks!

 

[mumps]

It is hard to work with pictures. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets.
Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing'
and you will be given a link to the file that you can post here.

 

[shell2133]

Thanks for the info, hope this is ok STAFF LIST.xlsx

 

[mumps]

Try:

Sub CreateSheets()
    Application.ScreenUpdating = False
    Dim posWS As Worksheet, assWS As Worksheet, v As Variant, dic As Object, i As Long
    Set posWS = Sheets("Position")
    Set assWS = Sheets("Assignment")
    Set dic = CreateObject("Scripting.Dictionary")
    With posWS
        v = .Range("E4", .Range("E" & .Rows.Count).End(xlUp)).Value
        For i = LBound(v) To UBound(v)
            If Not dic.exists(v(i, 1)) Then
                dic.Add v(i, 1), Nothing
                .Range("A1").CurrentRegion.AutoFilter 5, v(i, 1)
                .AutoFilter.Range.Offset(1).Copy
                Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Pos-" & v(i, 1)
                Range("A4").PasteSpecial
                .Range("A1").AutoFilter
                .Range("A1:Y3").Copy Range("A1")
                Columns.AutoFit
                .Range("A1").CurrentRegion.AutoFilter 5, v(i, 1)
            End If
        Next i
        .Range("A1").AutoFilter
    End With
    dic.RemoveAll
    With assWS
        v = .Range("E2", .Range("E" & .Rows.Count).End(xlUp)).Value
        For i = LBound(v) To UBound(v)
            If Not dic.exists(v(i, 1)) Then
                dic.Add v(i, 1), Nothing
                .Range("A1").CurrentRegion.AutoFilter 5, v(i, 1)
                .AutoFilter.Range.Copy
                Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Assign-" & v(i, 1)
                Range("A1").PasteSpecial
                Columns.AutoFit
            End If
        Next i
        .Range("A1").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

 

[mumps]

I forgot to mention that you have some merged cells in your sheets. You should avoid using merged cells at all cost because they almost always create problems for Excel macros.

 

[shell2133]

Thank you for this but it's not quite what I'm wanting to do. I went to end up with a separate spreadsheet for each of the localities as I have to email it to the manager there. So from the Staff List example I want to end up with 3 different spreadsheets, saved in my documents for example, each with one position and assignment tab, with the spreadsheet name being the locality from column E. I hope this explains it better, apologies!

 

[mumps]

If I understood correctly, it would mean that I wouldn't need to use the existing "Assignment" sheet at all. The macro would use only the data in the "Position" sheet to create the files. Is this correct? What is the full path to the folder where you want to save the files?

 

[shell2133]

No I still need both sheets. What I do now, which I know is really long-winded, is filter on column E on the Position tab, delete everything except Locality 1, do the same on the Assignment tab, then save the sheet as Staff List Locality 1. I'll then re-open the original file and do the same again for each of the localities. So its that process I'm trying to make easier. Apologies again if I'm not explaining it very well. Folder is C:\Users\shell\Documents\

 

[mumps]

each with one position and assignment tab

The assignment sheet in each new file would contain the data for each locality. What would the position sheet in each new file contain?

 

[shell2133]

The Position sheet shows all the actual posts for each locality and then looks to the Assignment sheet to show if they're over or under staffed