VBA code to split worksheet with multiple sheets

shell2133

New Member
Joined
Jan 21, 2022
Messages
26
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Attachments

  • Assignment tab.PNG
    Assignment tab.PNG
    145 KB · Views: 17
  • position tab.PNG
    position tab.PNG
    89.1 KB · Views: 17

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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.
 
Upvote 0
Try:
VBA Code:
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0
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\
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top