I have a spreadsheet of various expense report data of
employees that travel a lot. What i would like to do is
set up a macro or something of the sort to do the
following:

In column E, every value over $500, I would like that row
coppied and pasted into another worksheet. So if in cell
e10 that employee has $550 entered. put him in a seperate
worksheet. I would need this for several columns.

Any thoughts you guys might have would be greatly
appreciated.

Thanks,
Steven

Steven,

In the code module for the worksheet on which you enter your
expense report data, put the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dest As Range
If Target.Cells.Count 1 Then
Exit Sub
End If
If Target.Column < 5 Then
Exit Sub
End If
If Target.Value = 500 Then
Set Dest = Worksheets("Sheet2").Cells(Rows.Count,
"A").End(xlUp)(2, 1)
Target.EntireRow.Copy Destination:=Dest
End If
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Steven" wrote in message
...
I have a spreadsheet of various expense report data of
employees that travel a lot. What i would like to do is
set up a macro or something of the sort to do the
following:

In column E, every value over $500, I would like that row
coppied and pasted into another worksheet. So if in cell
e10 that employee has $550 entered. put him in a seperate
worksheet. I would need this for several columns.

Any thoughts you guys might have would be greatly
appreciated.

Thanks,
Steven

Thanks Chip. This code will automatically stack multiple
rows under eachother in worksheet 2? and how do I adjust
the start point on worksheet 2?
Thanks Again,
Steven
-----Original Message-----
Steven,

In the code module for the worksheet on which you enter
your
expense report data, put the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dest As Range
If Target.Cells.Count 1 Then
Exit Sub
End If
If Target.Column < 5 Then
Exit Sub
End If
If Target.Value = 500 Then
Set Dest = Worksheets("Sheet2").Cells(Rows.Count,
"A").End(xlUp)(2, 1)
Target.EntireRow.Copy Destination:=Dest
End If
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Steven" wrote in
message
...
I have a spreadsheet of various expense report data of
employees that travel a lot. What i would like to do is
set up a macro or something of the sort to do the
following:

In column E, every value over $500, I would like that
row
coppied and pasted into another worksheet. So if in cell
e10 that employee has $550 entered. put him in a
seperate
worksheet. I would need this for several columns.

Any thoughts you guys might have would be greatly
appreciated.

Thanks,
Steven


.

Chip,

The whole thing works great. My last question is how do i
change the start point on worksheet2. I have been playing
with it and I can't figure it out.

Thanks,
Steven
-----Original Message-----
Thanks Chip. This code will automatically stack multiple
rows under eachother in worksheet 2? and how do I adjust
the start point on worksheet 2?
Thanks Again,
Steven
-----Original Message-----
Steven,

In the code module for the worksheet on which you enter
your
expense report data, put the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dest As Range
If Target.Cells.Count 1 Then
Exit Sub
End If
If Target.Column < 5 Then
Exit Sub
End If
If Target.Value = 500 Then
Set Dest = Worksheets("Sheet2").Cells(Rows.Count,
"A").End(xlUp)(2, 1)
Target.EntireRow.Copy Destination:=Dest
End If
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Steven" wrote in
message
.. .
I have a spreadsheet of various expense report data of
employees that travel a lot. What i would like to do is
set up a macro or something of the sort to do the
following:

In column E, every value over $500, I would like that
row
coppied and pasted into another worksheet. So if in
cell
e10 that employee has $550 entered. put him in a
seperate
worksheet. I would need this for several columns.

Any thoughts you guys might have would be greatly
appreciated.

Thanks,
Steven


.

.

Steven

The whole thing works great. My last question is how do i
change the start point on worksheet2. I have been playing
with it and I can't figure it out.

Set Dest = Worksheets("Sheet2").Cells(Rows.Count,
"A").End(xlUp)(2, 1)

This line starts at the last cell in column A, goes up until it finds a cell
with data in it, then goes down 2 and to the right 1. If you want the first
row to be, say, B10, then after this line put

If Dest.Row < 10 Then Set Dest = Worksheets("Sheet2").Cells(10,"B")

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Thank you sir. So I lied, I have one more question. If i
run this in several different columns...How do I keep it
from entering dupiclate entries?

Thanks,
Steven
-----Original Message-----
Steven

The whole thing works great. My last question is how do
i
change the start point on worksheet2. I have been
playing
with it and I can't figure it out.

Set Dest = Worksheets("Sheet2").Cells
(Rows.Count,
"A").End(xlUp)(2, 1)

This line starts at the last cell in column A, goes up
until it finds a cell
with data in it, then goes down 2 and to the right 1. If
you want the first
row to be, say, B10, then after this line put

If Dest.Row < 10 Then Set Dest = Worksheets
("Sheet2").Cells(10,"B")

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com


.