copy cells from sheets based on date, paste to another sheet

I am not sure if this will solve your problems, but I would assign Today = Date, not Now. Date is an integer for today, Now is a decimal. The way your code is written I believe Today would always be > txbDate.value, unless that value is tomorrow or later. Also, if txbDate is a text box, you may want to go CDate(txbDate.value) to turn the string into a date value.

Does that help?

Originally Posted by upod

Although true for most cases it is possible for the user to set up the schedule for future commitments.

I just want to be sure you understand what happens when the current date is put into cbxDate.

Do you have the code in a module? That is where it should be.

I have now created a separate module which is called from the worksheet change event. This appears to run further but now gets stuck where I am copying data. Annotated below.

Please Login or Register  to view this content.

Presumably this is not the correct way to copy the cells of the rows that meet the if criteria. So how do I do this?

Right, the problem is you have activecell on both sides of the equal sign. The ActiveCell property only identifies the active cell on the active sheet. So if CoA is not the active sheet, Sheets("CoA").ActiveCell doesn't make any sense.
Can you use the cell index instead? For example:

Please Login or Register  to view this content.

Then you need to assign a variable to stand for that cell you are trying to copy. Its hard to tell where because your code is too spaced out but:
I think right here you'd go

Please Login or Register  to view this content.

You need to dim CellAddress as a String.

Then when you get to the line that was causing you trouble, you should be able to go

Please Login or Register  to view this content.

I can't see how the date would get pasted but the code would still fail on that line. Did you alter the lines directly after that line? I noticed they also have the activecell property on both sides of the equal sign.

Hmm, I can't think of a reason that it would fail. After you get the error, what does it say CellAddress is equal to when you hold the cursor over it while debugging?

Otherwise if you could post the workbook, that'd make it easier to troubleshoot.

Ok, I see what the problem is. Give me a bit to fix it, I'm pretty busy with my real job today.

Ok, I rewrote your code. Here are a couple of issues:
1) You don't need to select things. Look closely at how I reworked all the "Select" code to make it more efficient. It will run much faster this way.
2) Your Frequency select between months cells don't work. The Month() function tells you what month the date you put in is in. For example, Month(Date) = 10 (October is the tenth month). Dates are stored as integers in excel. Since you put 1 and 3 in those cells, you are actually looking at 1/1/1900 and 1/3/1900. If you want to add a month, put 30 in those cells and just add the cell value instead of using the month code.
3) The reason it was giving you this last error was the scope of the variable was local. To make it work, above both modules define it as public. For example: Public yourvariable as String.

Go through this code and see the changes carefully. I put the revised code near the commented out lines of your code so you could see how they correspond. Then after, go ahead and delete out your code. If it is then not working properly, let me know and we'll tweak some more.

Please Login or Register  to view this content.