split sheet into tabs in EXCEL

[Guest]

I am looking for a function that will break up a spreadsheet into separate
tabs, using a field. I dont know how much time I have wasted when management
wants "all category A in one tab, all category B in the next tab, and so on
thru category Z." It is all the same spreadsheet, with each category in a
separate tabbed sheet. So I spend hours cutting and pasting. Then, there is
some small change in the input data spreadsheet, so I have to start all over
again.

 

[Don Guillett]

Your life will be a lot easier if you just use
data>filter>autofilter>filter on category>work with the data

However, if they INSIST, use a macro to do the same thing for each category
and copy to the tabs. Or even a worksheet_change event that would
automatically copy and append the relevant data with each entry.

 

[Bernard Liengme]

Have you thought of using Data | Filter | AutoFilter?
Then you can have the data on one sheet but with a click of the mouse
display just category A, or B, or Z
Might impress the boss

Tell him/her it is poor data management to have the same data in two places.

BTW: please talk about "worksheets" - "tabs" are the thingies (that's a tech
term) for the itty-bitty things you click to open a worksheet. Knowing the
correct terminology improves communication with other users and, most
importantly, enable you to use Help better.

best wishes

 

[Zone]

Joe,
Assumptions:
1. Your original list is in the workbook's first sheet, with categories in
column A
2. You already have 26 additional sheets, each sheet named with the
category name
(for a total of 27 sheets)
3. The first row of every sheet contains the heading row
If these assumptions are okay, save a copy of the file in case there's a
problem. Copy this code, insert a standard module, and paste the code in
there. HTH, James

Sub Categorize()
Dim k As Long, ShtNm As String, BtmRow As Long
For k = 2 To 27
Worksheets(k).Range("a2:iv65536").ClearContents
Next k
ThisWorkbook.Worksheets(1).Activate
For k = 2 To Cells(Rows.Count, "a").End(xlUp).Row
ShtNm = Cells(k, "a")
BtmRow = Worksheets(ShtNm).Cells(Rows.Count, "a").End(xlUp).Row
Rows(k).EntireRow.Copy _
Destination:=Worksheets(ShtNm).Cells(BtmRow + 1, "a")
Next k
End Sub

 

[Dave Peterson]

You may find some tips here:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

 

[Guest]

Bernard
thanks for the filter advice, I will try that.
However, in every shop that I have worked, multiple spreadsheets in a master
spreadsheet are called "tabs". That is just the way it is. the whole file
is called a "spreadsheet", and each individual page is a "tab". The only
place that you find tabs called "worksheets" is in the documentation.
Thanks
Joe

 

[Don Guillett]

In excel the proper terminology is
file = workbook
part of file(workbook) = worksheet or sheet

Tab and page are sometimes used when referring to sheets but IF you want
help here it is best to use the proper terminology for common understanding.
You need to correct those people in "every shop that I have worked".

 

[Guest]

Hi Don,
Auto Filter option will resolve your question but if you want to populate
the results (by category) to multiple worksheets (for some purpose..)
You can use PIVOT option.
go for a Pivot table with master data and use the key field (that has the
category info)
Pull the Key (category) field to row and all remaining columns to Data field.
On the resulted pivot table you will have grouped categories. If you doble
click on the corresponding Total columns (last column of the pivot) you will
have the complete data of the category in a new worksheet (or tab).
Try & comment..!

 

[Don Guillett]

I'm just not fond of pivot tables.

 

[Wayne]

HI Guys,

Just to join in on this conversation. I am quite familiar with pivot tables
and have used them a lot for this type of analysis that is spoken of below.
My issue is also seperating each category into seperate tabs or worksheets
within the same excel file.

I believe that there is functinoality that will take the column containing
the category data and rather than do the double click multiple times to
capture each category into a new tab or worksheet, you can actually do this
automatically (and have each tab or worksheet named according to the
category).

Is anyone aware of this functinoality and how activate it? I look forwardto
your reply.

Regards
Wayne

 

[Roger Govier]

Hi Wayne

The functionality you refer to exists for Page Fields not Column Fields.

If you have a Page field set up, then from the PT Toolbar dropdown, select
Show Pages.
This will create a new tab in the workbook with all of the data relating to
each of the Page fields.

 

[KimB]

Roger - you are a life-saver! Excel 2003 took the "show pages" option out of
its standard Pivot Table menus, and I didn't think to customize the toolbar
to add the option, nor could I remember what MS called the feature.

Everyone else, "Show Pages" command can be found under the DATA category -
just drag it to any of your toolbars. Click on your page field header in the
pivot table, then the "show page" button - you will automatically get
separate tabs for your page field subjects.

 

[wal50]

Thanks Zone. Just what I needed
wal50

Joe,
Assumptions:
1. Your original list is in the workbook's first sheet, with categories in
column A
2. You already have 26 additional sheets, each sheet named with the
category name
(for a total of 27 sheets)
3. The first row of every sheet contains the heading row
If these assumptions are okay, save a copy of the file in case there's a
problem. Copy this code, insert a standard module, and paste the code in
there. HTH, James

Sub Categorize()
Dim k As Long, ShtNm As String, BtmRow As Long
For k = 2 To 27
Worksheets(k).Range("a2:iv65536").ClearContents
Next k
ThisWorkbook.Worksheets(1).Activate
For k = 2 To Cells(Rows.Count, "a").End(xlUp).Row
ShtNm = Cells(k, "a")
BtmRow = Worksheets(ShtNm).Cells(Rows.Count, "a").End(xlUp).Row
Rows(k).EntireRow.Copy _
Destination:=Worksheets(ShtNm).Cells(BtmRow + 1, "a")
Next k
End Sub