populating mulitple worksheets

R

runsrealfast

I have a book that has 4 sheets in it. Master, DEAD, WON, PENDING. In
the master sheet there is a list of sales opportunites, and as the
name sugests its a master list regardless of status. There is one
column named status that would be DEAD WON, or PENDING. (you might
guess where i am going with this) I would like after populating the
master file the correct sheet to be automatically populated based on
the status. Or is a status changes then auto populate the correct
sheet and delete from the sheet it no longer fits into. I'm sure there
is some sort of macro but I can't figure it out. Please help!

John
 
K

KC Rippstein

You might try using pivot tables for DEAD, WON, and PENDING. Set each one
up to auto-refresh at whatever interval you choose.

I tried really hard to make you a macro but cannot figure it out either.
 
B

Bill Kuunders

Or just forget about the three sheets.......
Just have the master and sort it by "status", or filter it by status.
 
B

Bill Kuunders

Of course you can do the filter bit with a macro

something like this....
You can change sheetnames columns and field number
as required I only had two columns to check whether it would work.

Sheets("Sheet1").Select
Columns("A:B").Select
Selection.AutoFilter Field:=2, Criteria1:="dead"
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste

Sheets("Sheet1").Select
Selection.AutoFilter Field:=2, Criteria1:="pending"
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
ActiveSheet.Paste

Sheets("Sheet1").Select
Selection.AutoFilter Field:=2, Criteria1:="won"
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet4").Select
ActiveSheet.Paste

Sheets("Sheet1").Select
Range("D7").Select
Application.CutCopyMode = False
End Sub
 
Top