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.

Your life will be a lot easier if you just use
datafilterautofilterfilter on categorywork 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.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Joe" wrote in message
...
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.

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

"Joe" wrote in message
...
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.

Thanks Zone. Just what I needed
wal50

"Zone" wrote:

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

"Joe" wrote in message
...
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.

You may find some tips he

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

Joe wrote:

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.

--

Dave Peterson

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Joe" wrote in message
...
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.

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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Joe" wrote in message
...
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.

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".

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Joe" wrote in message
...
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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Joe" wrote in message
...
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.

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" wrote:

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".

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Joe" wrote in message
...
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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Joe" wrote in message
...
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.

I'm just not fond of pivot tables.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Nathan Jag" wrote in message
...
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" wrote:

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".

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Joe" wrote in message
...
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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Joe" wrote in message
...
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.

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

"Nathan Jag" wrote:

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" wrote:

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".

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Joe" wrote in message
...
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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Joe" wrote in message
...
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.