Expense report problem.
 

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

Expense report problem.
 

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

Expense report problem.
 

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


.

Expense report problem.
 

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


.

.

Expense report problem.
 

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

Expense report problem.
 

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


.

Expense report problem.
 

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?

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

"Steven" wrote in message
...
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


.

Expense report problem.
 

Dick,
It all right to email you the attatchment so you can see
what i am working with?
-----Original Message-----
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?

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

"Steven" wrote in
message
...
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


.



.

Expense report problem.
 

Yes.

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

wrote in message
...
Dick,
It all right to email you the attatchment so you can see
what i am working with?
-----Original Message-----
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?

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

"Steven" wrote in
message
...
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


.



.