Seperate data to individual worksheets within same workbook

S

Sarahcox01

New Member
Joined
Oct 24, 2017
Messages
2
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.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
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.
 
Upvote 0
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.

[TABLE="width: 658"]
<tbody>[TR]
[TD="class: xl63, width: 25, bgcolor: transparent"]CO
[/TD]
[TD="class: xl63, width: 41, bgcolor: transparent"]PROC
[/TD]
[TD="class: xl63, width: 45, bgcolor: transparent"]EMP #
[/TD]
[TD="class: xl63, width: 204, bgcolor: transparent"]FULL-NAME
[/TD]
[TD="class: xl63, width: 41, bgcolor: transparent"]SHIFT
[/TD]
[TD="class: xl63, width: 85, bgcolor: transparent"]DEPT
[/TD]
[TD="class: xl63, width: 89, bgcolor: transparent"]JOB-CODE
[/TD]
[TD="class: xl63, width: 54, bgcolor: transparent"]STATUS
[/TD]
[TD="class: xl63, width: 79, bgcolor: transparent"]TERM-DATE
[/TD]
[TD="class: xl63, width: 109, bgcolor: transparent"]ANNIVERS-DATE
[/TD]
[TD="class: xl63, width: 103, bgcolor: transparent"]LAST-DAY-PAID
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent"]8180
[/TD]
[TD="bgcolor: transparent, align: right"]3444
[/TD]
[TD="bgcolor: transparent"]DIGGS, MELVIN
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"]31A
[/TD]
[TD="bgcolor: transparent"]FMR
[/TD]
[TD="bgcolor: transparent"]07
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]03/21/2000
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]08/04/2017
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent"]8180
[/TD]
[TD="bgcolor: transparent, align: right"]14243
[/TD]
[TD="bgcolor: transparent"]STEWART JR, STEPHEN H.
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"]31A
[/TD]
[TD="bgcolor: transparent"]PANEL
[/TD]
[TD="bgcolor: transparent"]07
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]03/17/2010
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]08/04/2017
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent"]8180
[/TD]
[TD="bgcolor: transparent, align: right"]18803
[/TD]
[TD="bgcolor: transparent"]MANUEL, OTIS
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"]31A
[/TD]
[TD="bgcolor: transparent"]FM
[/TD]
[TD="bgcolor: transparent"]07
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]05/20/2013
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]08/04/2017
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent"]8180
[/TD]
[TD="bgcolor: transparent, align: right"]20076
[/TD]
[TD="bgcolor: transparent"]DANIELS, ANTQUANE V.
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"]31A
[/TD]
[TD="bgcolor: transparent"]FM
[/TD]
[TD="bgcolor: transparent"]07
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]03/07/2016
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]08/04/2017
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]31A Count
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent"]8180
[/TD]
[TD="bgcolor: transparent, align: right"]6962
[/TD]
[TD="bgcolor: transparent"]OGLESBY, CURTIS T.
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"]32A
[/TD]
[TD="bgcolor: transparent"]FMDE
[/TD]
[TD="bgcolor: transparent"]07
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]08/07/2001
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]08/04/2017
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent"]8180
[/TD]
[TD="bgcolor: transparent, align: right"]12217
[/TD]
[TD="bgcolor: transparent"]WARD, TONY
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"]32A
[/TD]
[TD="bgcolor: transparent"]FMDE
[/TD]
[TD="bgcolor: transparent"]07
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]07/18/2005
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]08/04/2017
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent"]8180
[/TD]
[TD="bgcolor: transparent, align: right"]13434
[/TD]
[TD="bgcolor: transparent"]CARLISLE, BOBBY G.
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"]32A
[/TD]
[TD="bgcolor: transparent"]FMDE
[/TD]
[TD="bgcolor: transparent"]07
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]06/12/2006
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]08/04/2017
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent"]8180
[/TD]
[TD="bgcolor: transparent, align: right"]14063
[/TD]
[TD="bgcolor: transparent"]WILLIAMS, KEMMIE
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"]32A
[/TD]
[TD="bgcolor: transparent"]FMDE
[/TD]
[TD="bgcolor: transparent"]07
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]01/23/2007
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]08/04/2017
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent"]8180
[/TD]
[TD="bgcolor: transparent, align: right"]14326
[/TD]
[TD="bgcolor: transparent"]STEWART, JAMES F.
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"]32A
[/TD]
[TD="bgcolor: transparent"]FMDE
[/TD]
[TD="bgcolor: transparent"]07
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]04/23/2007
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]08/04/2017
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent"]8180
[/TD]
[TD="bgcolor: transparent, align: right"]18624
[/TD]
[TD="bgcolor: transparent"]CRAWLEY, JOHNATHAN L.
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"]32A
[/TD]
[TD="bgcolor: transparent"]FMLO
[/TD]
[TD="bgcolor: transparent"]07
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]09/17/2012
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]08/04/2017
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent"]8180
[/TD]
[TD="bgcolor: transparent, align: right"]20053
[/TD]
[TD="bgcolor: transparent"]MURPHY, THOMAS F.
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"]32A
[/TD]
[TD="bgcolor: transparent"]MECHSHOP
[/TD]
[TD="bgcolor: transparent"]07
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]02/15/2016
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]08/04/2017
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]32A Count
[/TD]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent"]8180
[/TD]
[TD="bgcolor: transparent, align: right"]766
[/TD]
[TD="bgcolor: transparent"]COTTON, RONNIE
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"]34A
[/TD]
[TD="bgcolor: transparent"]MATFM
[/TD]
[TD="bgcolor: transparent"]07
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]10/19/1998
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]08/04/2017
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent"]8180
[/TD]
[TD="bgcolor: transparent, align: right"]16970
[/TD]
[TD="bgcolor: transparent"]PHILLIPS, TERRY L.
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"]34A
[/TD]
[TD="bgcolor: transparent"]MATFM
[/TD]
[TD="bgcolor: transparent"]07
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]02/04/2009
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]08/04/2017
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]34A Count
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent"]8180
[/TD]
[TD="bgcolor: transparent, align: right"]18680
[/TD]
[TD="bgcolor: transparent"]WILLIS, THOMAS E.
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"]42A
[/TD]
[TD="bgcolor: transparent"]BREEDER
[/TD]
[TD="bgcolor: transparent"]07
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]11/19/2012
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]08/04/2017
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]42A Count
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent"]8180
[/TD]
[TD="bgcolor: transparent, align: right"]20622
[/TD]
[TD="bgcolor: transparent"]MOORE, MARY E.
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"]43A
[/TD]
[TD="bgcolor: transparent"]BREEDER
[/TD]
[TD="bgcolor: transparent"]07
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]01/30/2017
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]08/04/2017
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]43A Count
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent"]8180
[/TD]
[TD="bgcolor: transparent, align: right"]16973
[/TD]
[TD="bgcolor: transparent"]JONES, JAMES E.
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"]45A
[/TD]
[TD="bgcolor: transparent"]LIVE2
[/TD]
[TD="bgcolor: transparent"]07
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]02/18/2009
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]08/04/2017
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent"]8180
[/TD]
[TD="bgcolor: transparent, align: right"]17918
[/TD]
[TD="bgcolor: transparent"]FORTE, EDDIE J.
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"]45A
[/TD]
[TD="bgcolor: transparent"]BREEDER
[/TD]
[TD="bgcolor: transparent"]07
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]08/25/2010
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]08/04/2017
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi & welcome to the board.
Does this do what you need.
Change the values in red to match your sheet names
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
VBA to show and change list of names depending on selection
Replies
7
Views
736
mumps
mumps
S
  • Solved
How to make the formula in column D E F be more dynamic when the number in column C changed into different row?
Replies
2
Views
185
suloveh68
S
E
  • Solved
Copy a certain set of data from one sheet to another depending on the date typed in
2
Replies
10
Views
507
myall_blues
myall_blues
S
  • Question Question
Copying data into next available column to the right using macros?
Replies
3
Views
288
RobP
R
W
  • Question Question
Task Management Calc
Replies
1
Views
189
Woejeber
W

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

Share this page

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