Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Canadian GST Expense Report Templates | Excel Discussion (Misc queries) | |||
Travel Expense report | Excel Discussion (Misc queries) | |||
How to create summary of expense report? | Excel Worksheet Functions | |||
Expense report template | Excel Discussion (Misc queries) | |||
Help of Expense Report Question | Excel Discussion (Misc queries) |