Copying line from one sheet to another based on given criteria

[Valerie]

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?

 

[Tom Hutchins]

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

 

[Tom Hutchins]

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