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

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

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

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 -