Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying line from one sheet to another based on given criteria
Hello!
I create a sales stack ranking for a sales force that has a main page with everyones numbers across the nation. I also have a worksheet that has each sales persons numbers broken out by region. I want to know of a way to have a worksheet for florida read the main stack ranking page and pull lines of data that have "florida" in column g. Is there a way to do this? Right now I am making 10 copies of the main page, for 10 different regions, and then deleting the information on each WS that isn't for the specific region. This is really time consuming! Anyone know how to do this? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying line from one sheet to another based on given criteria
Check this page on the Contextures site:
http://www.contextures.com/excelfiles.html In the Filters section there are several sample workbooks which use macros to execute the advanced filter to copy records from a master sheet to individual sheets. For example: Create New Sheets from List -- uses an Advanced Filter to create separate sheet of orders for each sales rep; macro automates the filter. AdvFilterRep.zip I think you find something there you can adapt. Let me know (via reply to this thread) if you need help. Hope this helps, Hutch "Valerie" wrote: Hello! I create a sales stack ranking for a sales force that has a main page with everyones numbers across the nation. I also have a worksheet that has each sales persons numbers broken out by region. I want to know of a way to have a worksheet for florida read the main stack ranking page and pull lines of data that have "florida" in column g. Is there a way to do this? Right now I am making 10 copies of the main page, for 10 different regions, and then deleting the information on each WS that isn't for the specific region. This is really time consuming! Anyone know how to do this? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying line from one sheet to another based on given criteria
Here's another way - a macro I wrote years ago, before I knew about advanced
filters. Check the assumptions & instructions in the code comments. Sub SplitData() 'Extracts data for multiple entities (customers, brands, ??) 'from a master sheet to separate sheets for each entity. 'Assumptions in the code: '1. ENTITY NAME/ID IS IN KEY COLUMN '2. SHEET HAS HEADINGS IN ONE ROW ONLY '3. THERE IS A HEADING FOR EVERY COLUMN WITH DATA '4. DATA IS ALREADY SORTED BY KEY COLUMN '5. MASTER DATA SHEET IS ACTIVE WHEN MACRO IS RUN '---------------------------------------------------------- 'To use this macro: 'A) Open this file. 'B) Open the Excel file with the data. Make sure the correct ' sheet is active. 'C) Run the SplitData macro. '---------------------------------------------------------- 'Set values for constants Const HdgRow = 1 Const KeyCol = "G" 'Declare variables. Dim CellRef1 As Object, BaseSht As String Dim a As Integer, x As Integer, MT As Integer Dim CurrID As String, PrevID As String Dim EndCol As Integer, KeyColNbr As Integer 'Store the name of the starting sheet BaseSht$ = ActiveSheet.Name Range(KeyCol & (HdgRow + 1)).Activate a% = ActiveCell.Row 'Assign the first entity ID as PrevID (so have a value to compare). PrevID$ = ActiveCell.Value 'Find the last data column (with a heading). EndCol% = Cells(HdgRow, Columns.Count).End(xlToLeft).Column 'Get the number of the KeyCol KeyColNbr% = Columns(KeyCol).Column MT% = 0 'Go to first row of data in key column. 'Walk down column and test value of every cell. Stop when 100 'consecutive empty cells are encountered. Do While MT% < 100 Set CellRef1 = Cells(a%, KeyColNbr%) CellRef1.Activate CellRef1.Select CurrID$ = CellRef1.Value 'If the current cell is empty, add 1 to MT, the empty cell counter. If CurrID$ = "" Then MT% = MT% + 1 Else 'If the current cell is not empty, reset MT. Check if its value '(CurrID$) is the same as the previous row (PrevID$). If it's not 'the same, copy cols 1 through EndCol% for all the PrevID$ rows '(including row 1). Paste them onto a new sheet, then return to the 'original sheet (BaseSht$). Delete all the PrevID$ rows (but not row '1). Assign the new CurrID$ to PrevID$. Reset a% to 1 (first row; 'Will then increment it). MT% = 0 If CurrID$ < PrevID$ Then Range(Cells(1, 1), Cells(a% - 1, EndCol%)).Select Selection.Copy Sheets.Add ActiveSheet.Paste ActiveSheet.Name = Cells(2, KeyColNbr%).Value Sheets(BaseSht$).Activate Range(Cells(2, 1), Cells(a% - 1, EndCol%)).Select Selection.EntireRow.Delete PrevID$ = CurrID$ a% = 1 End If End If a% = a% + 1 Loop 'Return to the starting sheet & rename it for the last set of data. Sheets(BaseSht$).Activate ActiveSheet.Name = Cells(2, KeyColNbr%).Value End Sub Hope this helps, Hutch "Valerie" wrote: Hello! I create a sales stack ranking for a sales force that has a main page with everyones numbers across the nation. I also have a worksheet that has each sales persons numbers broken out by region. I want to know of a way to have a worksheet for florida read the main stack ranking page and pull lines of data that have "florida" in column g. Is there a way to do this? Right now I am making 10 copies of the main page, for 10 different regions, and then deleting the information on each WS that isn't for the specific region. This is really time consuming! Anyone know how to do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying data from one worksheet to another based on criteria | Excel Discussion (Misc queries) | |||
Lookup function and compare | Excel Discussion (Misc queries) | |||
copying sheet references that refer to a cell in the preceding she | Excel Worksheet Functions | |||
Returning Results Based on Two Criteria | Excel Worksheet Functions | |||
How do I get info from a cell based on criteria from another cell | Excel Worksheet Functions |