Seperate data to individual worksheets within same workbook

[Sarahcox01]

I use the attached roster everyday. Several times a week i have to break it down by departments. However all the depts starting with 3 goes on one, 4 on another etc etc. How can i easily make excel do this? My department number are all in the same column.

 

[alansidman]

You have not provided us with any sample data to look at. Please show us a sample of what you have and using that data explain what you wish to happen.

 

[Sarahcox01]

I dont see how to attach a spreadsheet but is here is a sample of the data. The actual roster has 1300 lines on it. We run a query into excel. It does come in with sub-totals. I was hopefully I could use that, but if not I can stop the sub-totals. Also - remember all the DEPT# with 3's should go on one sheet and so forth. Your help is greatly appreciated.

CO	PROC	EMP #	FULL-NAME	SHIFT	DEPT	JOB-CODE	STATUS	TERM-DATE	ANNIVERS-DATE	LAST-DAY-PAID
7	8180	3444	DIGGS, MELVIN	1	31A	FMR	07		03/21/2000	08/04/2017
7	8180	14243	STEWART JR, STEPHEN H.	1	31A	PANEL	07		03/17/2010	08/04/2017
7	8180	18803	MANUEL, OTIS	1	31A	FM	07		05/20/2013	08/04/2017
7	8180	20076	DANIELS, ANTQUANE V.	1	31A	FM	07		03/07/2016	08/04/2017
					31A Count	4
7	8180	6962	OGLESBY, CURTIS T.	1	32A	FMDE	07		08/07/2001	08/04/2017
7	8180	12217	WARD, TONY	1	32A	FMDE	07		07/18/2005	08/04/2017
7	8180	13434	CARLISLE, BOBBY G.	1	32A	FMDE	07		06/12/2006	08/04/2017
7	8180	14063	WILLIAMS, KEMMIE	1	32A	FMDE	07		01/23/2007	08/04/2017
7	8180	14326	STEWART, JAMES F.	1	32A	FMDE	07		04/23/2007	08/04/2017
7	8180	18624	CRAWLEY, JOHNATHAN L.	1	32A	FMLO	07		09/17/2012	08/04/2017
7	8180	20053	MURPHY, THOMAS F.	1	32A	MECHSHOP	07		02/15/2016	08/04/2017
					32A Count	7
7	8180	766	COTTON, RONNIE	1	34A	MATFM	07		10/19/1998	08/04/2017
7	8180	16970	PHILLIPS, TERRY L.	1	34A	MATFM	07		02/04/2009	08/04/2017
					34A Count	2
7	8180	18680	WILLIS, THOMAS E.	1	42A	BREEDER	07		11/19/2012	08/04/2017
					42A Count	1
7	8180	20622	MOORE, MARY E.	1	43A	BREEDER	07		01/30/2017	08/04/2017
					43A Count	1
7	8180	16973	JONES, JAMES E.	1	45A	LIVE2	07		02/18/2009	08/04/2017
7	8180	17918	FORTE, EDDIE J.	1	45A	BREEDER	07		08/25/2010	08/04/2017

<tbody>
</tbody>

 

[Fluff]

Hi & welcome to the board.
Does this do what you need.
Change the values in red to match your sheet names

Sub CopyToSheets()

    Dim Cl As Range
    
    With Sheets("[COLOR=#ff0000]Roster[/COLOR]")
        For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
            Select Case Left(Cl.Offset(, 5).Resize(1).Value, 1)
                Case 3
                    Cl.Resize(, 11).Copy Sheets("[COLOR=#ff0000]3[/COLOR]").Range("A" & Rows.Count).End(xlUp).Offset(1)
                Case 4
                    Cl.Resize(, 11).Copy Sheets("[COLOR=#ff0000]4[/COLOR]").Range("A" & Rows.Count).End(xlUp).Offset(1)
            End Select
        Next Cl
    End With

End Sub

It assumes that your data starts in A2, with the header in Row 1