Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy rows based on specific criteria
Hello, I have a workbook with multiple projectsheets, one for each project I'm working on. One of the things I have on the projct sheets is my personal action list. This list includes several rows. Per Row I have information like: nr., action item, date raised and date due. In addition to the project sheets I have one MAIN sheet. On this main sheet I want to see a list of all action items (= copy the whole row) that are due within e.g. 3(working) days from today. This way I don't have to go through all tabs to see what action items needs my immediate attention. Can anyone help me start out on this? Thanks, Alvin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy rows based on specific criteria
You could add a named cell "TodaysDate" to the workbook, which refers
to =TODAY(). Then add a helper column to each worksheet with a formula like: =A1- TodaysDate , where A1 is the due date. Fill down the formula so it calculates the number of days each action item is due. Then apply autofilter to the entire list, and filter the new formula you column you just created: Custom, Is Less Than, 3 Then copy and paste those rows into the main sheet. Does that help? --JP On Aug 8, 9:40*am, Alvin wrote: Hello, I have a workbook with multiple projectsheets, one for each project I'm working on. One of the things I have on the projct sheets is my personal action list. This list includes several rows. Per Row I have information like: nr., action item, date raised and date due. In addition to the project sheets I have one MAIN sheet. On this main sheet I want to see a list of all action items (= copy the whole row) that are due within e.g. 3(working) days from today. This way I don't have to go through all tabs to see what action items needs my immediate attention. Can anyone help me start out on this? Thanks, Alvin |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy rows based on specific criteria
Hi JP,
thanks for your reply. Regardint he auto-filter is that something I can automate? Or do I manually need to select the filter? And what about copying? How do I copy from all sheets, all lines where difference in days <3 ? Thanks. "JP" wrote: You could add a named cell "TodaysDate" to the workbook, which refers to =TODAY(). Then add a helper column to each worksheet with a formula like: =A1- TodaysDate , where A1 is the due date. Fill down the formula so it calculates the number of days each action item is due. Then apply autofilter to the entire list, and filter the new formula you column you just created: Custom, Is Less Than, 3 Then copy and paste those rows into the main sheet. Does that help? --JP On Aug 8, 9:40 am, Alvin wrote: Hello, I have a workbook with multiple projectsheets, one for each project I'm working on. One of the things I have on the projct sheets is my personal action list. This list includes several rows. Per Row I have information like: nr., action item, date raised and date due. In addition to the project sheets I have one MAIN sheet. On this main sheet I want to see a list of all action items (= copy the whole row) that are due within e.g. 3(working) days from today. This way I don't have to go through all tabs to see what action items needs my immediate attention. Can anyone help me start out on this? Thanks, Alvin |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy rows based on specific criteria
You could automate the whole process, really.
To filter a range, use the Autofilter Method of the Range object, for example Dim rng as Excel.Range Set rng = Range(Range("A1"), Range("E1")) rng.Autofilter After you filter the rows based on the formula condition, all you need to do is highlight and copy those rows to the other worksheet. HTH, JP On Aug 11, 3:51*am, Alvin wrote: Hi JP, thanks for your reply. Regardint he auto-filter is that something I can automate? Or do I manually need to select the filter? And what about copying? How do I copy from all sheets, all lines where difference in days <3 ? Thanks. "JP" wrote: You could add a named cell "TodaysDate" to the workbook, which refers to =TODAY(). Then add a helper column to each worksheet with a formula like: =A1- TodaysDate , where A1 is the due date. Fill down the formula so it calculates the number of days each action item is due. Then apply autofilter to the entire list, and filter the new formula you column you just created: Custom, Is Less Than, 3 Then copy and paste those rows into the main sheet. Does that help? --JP On Aug 8, 9:40 am, Alvin wrote: Hello, I have a workbook with multiple projectsheets, one for each project I'm working on. One of the things I have on the projct sheets is my personal action list. This list includes several rows. Per Row I have information like: nr., action item, date raised and date due. In addition to the project sheets I have one MAIN sheet. On this main sheet I want to see a list of all action items (= copy the whole row) that are due within e.g. 3(working) days from today. This way I don't have to go through all tabs to see what action items needs my immediate attention. Can anyone help me start out on this? Thanks, Alvin- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I find and copy rows based on specific criteria? | New Users to Excel | |||
Copy data to another wb based on specific criteria. | Excel Programming | |||
Copy data to another wb based on specific criteria | Excel Discussion (Misc queries) | |||
Coping Rows based on specific criteria to designated pages. | Excel Programming | |||
I want to delete certain rows based on specific criteria | Excel Programming |