Expense report problem.

[Steven]

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 Pearson]

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]

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

 

[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

 

[Dick Kusleika]

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.

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")

 

[Steven]

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

 

[Dick Kusleika]

Steven

Just so I understand, you have an expense report and you want all entries
over $500 to get listed on another sheet. Does the report contain expenses
for more than one person? What time period does the spreadsheet cover?

Is there anything unique about the rows that you could use to search the
other sheet and see if it's already there?

Do you need to do this right when the entry is made, or can you do it once
per day, once per week, etc?

What do you do with the over $500 sheet?

 

[Guest]

Dick,
It all right to email you the attatchment so you can see
what i am working with?

 

[Dick Kusleika]

Yes. (e-mail address removed)