Link row of data from one worksheet to another based on column criteria

texas tina

unread,

Oct 10, 2012, 10:38:48 AM10/10/12

to

I have excel sheet with 4 worksheets tabs(Master, Won, Lost, No Bid).
All data entered on Master. I want each row to automatically
link/appear on 2nd, 3rd or 4th worksheet tab based on info in column Q
(Result) which would be either 'Pending' - in which case it would stay
on Master, 'Won' - would stay and copy to Won worksheet, 'Lost' - would
stay andcopy to Lost worksheet, 'No Bid' - would stay and copy to No Bid
worksheet. All rows would always show up on Master worksheet.

--
texas tina

Spencer101

unread,

Oct 11, 2012, 2:05:56 AM10/11/12

to

Could you post a sample workbook?

--
Spencer101

texas tina

unread,

Oct 12, 2012, 3:32:44 PM10/12/12

to

I have attached a sample file

Spencer101;1606258 Wrote:
> Could you post a sample workbook?

+-------------------------------------------------------------------+
|Filename: Sample.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=618|
+-------------------------------------------------------------------+

--
texas tina

Claus Busch

unread,

Oct 12, 2012, 4:07:44 PM10/12/12

to

Hi Tina,

Am Fri, 12 Oct 2012 19:32:44 +0000 schrieb texas tina:

> I have attached a sample file

try it with a macro:

Sub myCopy()
Dim LRow As Long
Dim FERow As Long
Dim i As Long
Dim wsh As Worksheet

For Each wsh In Worksheets
If wsh.Name <> "Master" Then
Sheets("Master").Rows(1).Copy wsh.Range("A1")
End If
Next

With Sheets("Master")
LRow = .Cells(.Rows.Count, "Q").End(xlUp).Row
For i = 2 To LRow
Select Case .Cells(i, "Q")
Case "Won"
FERow = Sheets("Won").Cells(Rows.Count, 1).End(xlUp).Row + 1
.Range(.Cells(i, 1), .Cells(i, 19)).Copy _
Destination:=Sheets("Won").Cells(FERow, 1)
Case "Lost"
FERow = Sheets("Lost").Cells(Rows.Count, 1).End(xlUp).Row + 1
.Range(.Cells(i, 1), .Cells(i, 19)).Copy _
Destination:=Sheets("Lost").Cells(FERow, 1)
Case "No Bid"
FERow = Sheets("No Bid").Cells(Rows.Count, 1).End(xlUp).Row + 1
.Range(.Cells(i, 1), .Cells(i, 19)).Copy _
Destination:=Sheets("No Bid").Cells(FERow, 1)
End Select
Next
End With
For i = 2 To Sheets.Count
Sheets(i).Columns("A:S").AutoFit
Next
End Sub

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Spencer101

unread,

Oct 13, 2012, 6:05:24 AM10/13/12

to

texas tina;1606305 Wrote:
> I have attached a sample file

Here's one simple approach to this. Column A on each worksheet holds a
simple reference that is used to look up the data from the Sheet1 into
the others. I've left them visible for now but you can simply hide
column A on each sheet.

The email address hyperlinks in sheets 2 - 4 don't work in this version,
but that's easily fixed if you need it to be.

The other way around this is simply recording a macro that filters the
data in sheet 1 then copying and pasting the data into the relevant
sheets from there.

Let me know if you need more help / information.

S.

+-------------------------------------------------------------------+
|Filename: TexasTina Example.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=622|
+-------------------------------------------------------------------+

--
Spencer101