excel move items to new tabs based on criteria

I have something like this:

cost center   date     amount
1001              May 5      15.00
2002             May 5        34.00
3003              May 6     44.00

I want to select 4 cost centers, and put their data into 4 different tabs (sheets). One for each cost center.

Can you please point me in the right direction?

I'd like to select 4 cost centers as part of the criteria.. as in... select cost center 2002, 6006, 7007, 9009... make a sheet for each and move all records for each into each according tab. It will always be the same 4 cost centers.
LVL 19
MontoyaProcess Improvement MgrAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamMicrosoft Excel ExpertCommented:
there are going to be two ways to do this.  easy way with Pivot table and then you pot the cost centre as page filter and then once pivot is created then under pivot table new contextual tab "Options" you select "show report filter pages" and it will generate each new worksheet for each cost centre.

hard way, will be VBA solution.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
A sample workbook with enough sample data would be helpful along with the desired output you are expecting.
MontoyaProcess Improvement MgrAuthor Commented:
I created a workbook. Here's a sample.

JimJam, I did the Pivot table ,but could not find the option you mentioned. Can you please assist?

Thank you!

here's a dummy workbook

Screenshot-2015-08-06-11.09.02.pngScreenshot-2015-08-06-11.08.38.pngsample.xlsx
Roy CoxGroup Finance ManagerCommented:
If you want a VBA solution then here's some code that I use regularly. It may need tweaking for your purposed, nut let me know if you need help to do that.

Option Explicit

'---------------------------------------------------------------------------------------
' Module    : Module1
' DateTime  : 24/09/2006 22:48
' Updated   : 2014
' Author    : Roy Cox (royUK)
' Website   :  more examples
' Purpose   :  Create a sheet for each unique name in data
' Disclaimer; This code is offered as is with no guarantees. You may use it in your
'             projects but please leave this header intact.
'---------------------------------------------------------------------------------------

Sub ExtractToSheets()
    Dim ws     As Worksheet
    Dim wsNew  As Worksheet
    Dim rData  As Range
    Dim rCl    As Range
    Dim sNm    As String
    Set ws = Sheet1

    'extract a list of unique names
    'first clear existing list
    With ws
        Set rData = .Range("A1").CurrentRegion
        .Columns(.Columns.Count).Clear
        rData.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Cells(1, .Columns.Count), Unique:=True

        For Each rCl In .Cells(1, .Columns.Count).CurrentRegion
            sNm = rCl.Text
            'add new sheet (only if required-NB uses UDF)
            If WksExists(sNm) Then
                'so clear contents
                Sheets(sNm).Cells.Clear
            Else
                'new sheet required
                Set wsNew = Sheets.Add
                wsNew.Move After:=Worksheets(Worksheets.Count)    'move to end
                wsNew.Name = sNm
            End If
            'AutoFilter & copy to relevant sheet
            rData.AutoFilter Field:=1, Criteria1:=sNm
            rData.Copy Destination:=Worksheets(sNm).Cells(1, 1)
        Next rCl
    End With
    ws.Columns(Columns.Count).ClearContents        'remove temporary list
    rData.AutoFilter        'switch off AutoFilter
End Sub


Function WksExists(wksName As String) As Boolean
    On Error Resume Next
    WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function

Open in new window

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
If you are open to the Pivot Table option, please follow these steps....

1) Insert a Pivot Table.

2). Drag Month field to the row area.

3) Drag Amount field to the Values area.

4) Drag Cost Center to the Filter area.

5) Now click on any cell inside the pivot table, you will see the Pivot Table Option tool at the top.

6) Click on Options.

7) On the top left of the Options Menu Bar, you will find a drop box named Options (just below the Pivot Table Name box), expand it.

8) Click on Show Report Filter Pages.

9) In the next window which is populated, click on OK.

This way you will get the report for each cost center on its own sheet and sheets added are named on the Cost Center so it is easy for you to identify that which sheet belongs to which cost center.

For details refer to the attached workbook.

Does this help?
PivotTable-sample.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.