Frustrated but determined!!!

Prestonlay

New Member
Joined
Oct 13, 2006
Messages
18
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!!!!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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

New Member
Joined
Oct 13, 2006
Messages
18
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

New Member
Joined
Oct 13, 2006
Messages
18

ADVERTISEMENT

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"
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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.
Code:
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?
 

Forum statistics

Threads
1,136,991
Messages
5,678,996
Members
419,797
Latest member
ikethegenius

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top