|
split sheet into tabs in EXCEL
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. |
split sheet into tabs in EXCEL
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. |
split sheet into tabs in EXCEL
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. |
split sheet into tabs in EXCEL
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. |
split sheet into tabs in EXCEL
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 |
split sheet into tabs in EXCEL
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. |
split sheet into tabs in EXCEL
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. |
split sheet into tabs in EXCEL
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. |
split sheet into tabs in EXCEL
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. |
split sheet into tabs in EXCEL
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. -- Regards Roger Govier "Wayne" wrote in message ... 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. |
split sheet into tabs in EXCEL
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. -- KimB "Roger Govier" wrote: 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. -- Regards Roger Govier "Wayne" wrote in message ... 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. |
split sheet into tabs in EXCEL
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. |
| All times are GMT +1. The time now is 07:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com