Copy data from row to a worksheet from multiple sheets based on Date

[Loppy]

Hello all,

I am hoping somebody can assist me with this.

A very long time ago, "NBVC" on these forums gave an answer to a question that is almost exactly what I need. It is here from 7-17-2009:

See attached.

Add a column in the All sheet (Column N) and in N5 entered formula:

=IF(A5="","",IF(K5<=TODAY()+60,MAX($N$4:N4)+1,""))

which counts the number of matches (within 60 days of today)..

formula is copied down to row 100 to accomodate future additions.. you can copy down even further.

then in Renewals sheet...in A2, use formula:

=IF(ROWS($A$2:$A2)>MAX(All!N:N),"",ROWS($A$2:$A2)) copied down to count count of rows that will be extracted... copy down as far as you want..

in B2, use formula and copy down as far as you want and across all columns...

=IF($A2="","",INDEX(All!A:A,MATCH($A2,All!$N:$N,0)))

no need for button.. live update.

I almost have the exact type thing but I have multiple sheets with dates on that I need to have checked and then return the row of data to a main sheet for expiring.

Basically, I have the main sheet of "Expiring Licenses". This is the sheet that will collect the data from the other sheets based on expiring within the next 2 months from the current date. All the other sheets are individual office workers with licenses in different states that have expiration dates. I want to be able to have the main sheet just show me the people and particular license that is going to be expiring in the next 2 months and move the data over to the main sheet. I only need the data shown in the Expiring Licenses Sheet from the Name Sheets. Once, the name sheet gets updated when they renew their license, the Expiring Licenses sheet automatically changes with the update. Also, on the Name sheets, I want to be able to have cells "D" "E" they are Merged and pull the name from the Tab below. I thought I had it updating even if I changed the tab name but it doesn't. I just wanted to anytime I change the tab name, to change in the merged column. I don't have an issue un-merging the name column either. If you could please assist me with this, that would be amazing. Thanks very much.

I've attached a dummy file in case it will help.

Thanks in advance,

Chris

[Logit]

.
This is a macro approach vs formula See if it accomplishes your goal :

Please Login or Register  to view this content.

[Loppy]

Logit,

Thanks so much for the reply and Help!!

The macro is pushing out strange results. When I click the macro button you created, it does populate the "Expiring Licenses" sheet. However, it appears to only look at the month and possibly day. It is showing me things are going to expire that are years out. In addition to that, if I add a license or update a licenses month, and click the review button, it updates and adds the new date to the "Expiring Licenses" sheet but it is now outside the 2 month window and still shows up. I also changed a License on one of the Name sheets to be within the set 2 months clicked on review and it doesn't show up at all. SO I am not sure if I need to edit anything or perhaps I did something bad on my end. Again, thanks for your help. And I apologize for maybe not doing this correctly.

Chris

[Logit]

.
Disregard the previous macro (Post #2). Use this macro instead :

Please Login or Register  to view this content.

[Loppy]

Logit,

Thanks for the quick reply and update. However, I cannot get it to work at all. I pasted this into the module that was currently there and saved but when I click review, nothing happens. I know I am missing a step. Sorry for the bother. Can you help me get the code into the sheet please sir?

Chris

[Logit]

.
See attached

[Loppy]

Logit,

Thanks again for helping.

This is getting closer to the result. I had to change this line:

Set Rng = .Range(.Cells(1, "D"), .Cells(Rws, "D"))
to
Set Rng = .Range(.Cells(3, "D"), .Cells(Rws, "D"))

I hope changing that is correct. I thought that meant row 3 column D if I changed it.

Also,

I see you changed some dates to 8-13-18. The only result that gets returned is the licenses with dates of 8-13-18. The dates we want to get back are anything from whatever day I review it (like today) to anything up to 60 days. So all the in-between also. I want to get a two month notice of these licenses expiring. When I change the days to <= 60, it returns everything. I am baffled at the moment. And I feel bad for pestering you. I really do appreciate the help though no doubt.

So is it maybe the line:

If c.Value = (Date + 60) Then 'searches for 60 days in future

That I am having issues with, or is it a formatting issue? I did turn the D columns to the Same Date formats also while testing the code.

Thanks in advance,

Chris

[Logit]

.
To return everything 60 days out and less, this is the line you will use :

Please Login or Register  to view this content.

I changed several dates on different sheets and tested. Works as expected.

RE :
Set Rng = .Range(.Cells(1, "D"), .Cells(Rws, "D"))
to
Set Rng = .Range(.Cells(3, "D"), .Cells(Rws, "D"))

Either one is fine, because it is always looking for a date and nothing else. However, your change : (.Cells(3, "D") is more accurate.
You are showing signs of a precise programmer. You have reason to pat yourself on the back now.

[Loppy]

Logit,

Thank you very much for the assistance in getting the correct formula. I had some minor surgery Friday and could not respond until today. I think this will work for what I need. I am going to test it out and am pretty sure it should work. Thanks again for helping and teaching me stuff on how to get the results I need.

Quick question...
Is there a way to also update results on opening the excel file? Most of the time, that is what needs to happen. And since the day I open it always changes, it'd be nice to have it auto-update. The only time I want to have to use the Review button is when I actually renew a license and edit the license date for the person. Other than that, if it auto-updated on opening, that would be awesome. Unless it will auto-update upon any editing of licenses on any sheets. Anyways, thanks again for the help. Very Knowledgeable you are.

Loppy

[Logit]

.
Glad you worked out the few changes on your own. I get to looking at so much code and my mind starts to wander. One of the wonderful things about retirement !

To have the workbook auto update when you open it ... in ThisWorkbook module you will add the following :

Please Login or Register  to view this content.

Then each time you open the workbook it will auto run the macro. You can always click the button any time you like as well.

Sorry I didn't get back to you sooner. The FORUM has been messing up lately and the server doesn't auto email me when an updated post occurs. I have to go back through my postings list and
manually check to see if anything has change.

Cheers !

[Loppy]

Logit,

Thank you very much for the help. Ha...I bet the mind starts to wander once retired...You have so much time just to think. But again, isn't that the goal, to get to retirement? hehe

Anyways, thanks again, this worked for me. Keep looking at all the code, at least it keeps your mind in shape!!

Thanks,

Loppy

[Logit]

.
You are welcome.