Frustrated but determined!!!

[Prestonlay]

I'm trying to have my sales filtered to different sheets off of one "master sheet". I have the sales rep under one column using a list and I when I select a specific rep I then want everything on that row to be transfered to a specific sheet. Is it possible.

I would love to eventually have everything based off a userform. But I am not there yet.

PLEASE HELP!!!!

 

[agihcam]

Hi -
Welcome to the board.
Few background regarding layout of your sheet and the result after would be of big help if you can post it on the board.

 

[HalfAce]

Hello Prestonlay, welcome to the board.
A couple questions for you so I can fully understand.
Are you asking for a way to simply select a cell (in the sales rep column) and have that entire row automatically copied/pasted to another sheet (perhaps a sheet named the same as the sales rep selected?)

If there is more than one row on your "master sheet" with the same sales rep, would you
want to copy all of the rows for each rep at the same time rather than having to select
each one individually?

 

[Prestonlay]

Here is The Info There are 186 different Insureds. This is just a couple.

# Insured INSURANCE EST AMOUNT SETTLED AMOUNT MOVED Our Fee Sales Sales Fee Sales Total
1 Adkins, Larry $10,000.00 $22,500.00 $12,500.00 $2,250.00 Carey 25% $562.50
2 Bauer, Matt $18,403.45 $64,521.60 $46,118.15 $6,452.16 Mike 10% $645.22
3 Franklin, Judy $9,053.15 $12,792.34 $3,739.19 $934.80 Preston 35% $327.18
4 Mathes, Marlin $9,533.45 $19,690.00 $10,156.55 $1,969.00 Mike 25% $492.25 [/size]
5 Santiago, Julian $- $- $- $- Robert 20% $-
6 Watkins, Stephanie $18,405.68 $25,166.45 $6,760.77 $1,690.19 Preston 10% $169.02 [/face][/face][/size]

 

[Prestonlay]

Baisically I want all of Carey's claims on sheet 2 - AKA "Carey"......

I want sheet 2/Carey to just have his claim like....

# Insured INSURANCE EST AMOUNT SETTLED AMOUNT MOVED Our Fee Sales Sales Fee Sales Total
1 Adkins, Larry $10,000.00 $22,500.00 $12,500.00 $2,250.00 Carey 25% $562.50

And the same for Sheet 3 - AKA - "Mike"

 

[Prestonlay]

Halface you are exactly right. Do I need to have a "Loop"?

 

[babycody]

See if this is what you are looking for http://archive-a02m01.libsyn.com/po...6864c23ad9918/4532fbd7/mrexcel/podcast291.mp4

 

[HalfAce]

Do I need to have a "Loop"?

In order to copy all instances of each name to their own sheet at the same time
then yes, looping down the proper column is what I was thinking.

Which column are those names (Mike, Carey, etc.) in?
And, will there always be an existing sheet for each name in that column, or will
there ever be the need to create a sheet for someone on the fly?

Assuming:
1) The rep's names are in column F
2) That there are already existing sheets for each rep name in column F
3) You have headers in row 1 of the master and all subsequent rep sheets
You can try something like this. The code is meant to be run with the 'master sheet'
being the active sheet.

Option Explicit

Sub Demo()
Dim LstRw As Long, rep As Range, repList As Object
LstRw = Cells(Rows.Count, "A").End(xlUp).Row
Set repList = CreateObject("Scripting.Dictionary")
ActiveSheet.AutoFilterMode = False

For Each rep In Range("F2:F" & LstRw)
  If Not repList.Exists(rep.Value) Then
    Columns("F:F").AutoFilter Field:=1, Criteria1:=rep.Value
    Range("A2:A" & LstRw).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
      Sheets(rep.Value).Cells(Rows.Count, "A").End(xlUp)(2)
    repList.Add rep.Value, Nothing
    ActiveSheet.AutoFilterMode = False
  End If
Next rep

Set repList = Nothing

End Sub

Does that get you close to what you want?