how to create a worksheet OPEN event to scan a sheet based on dates (expired)

[dmcgov]

this is a continuation of this POST:

so what i am looking for is the following:

Use the worksheet open event to scan the sheet "Closed POs" and look for expired dates (anything more than a month) in column D.

i already have the code for the change event and that works. what is needed now is the above.

can anyone help me out with this?

[xlnitwit]

Hi again.

I would recommend that you use a separate routine to process the cells like this

Please Login or Register  to view this content.

then call that as required

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

[dmcgov]

@xlnitwit,

your sub is named ProcessDates but the following two macros used ProcessCells. should i change ProcessCells to ProcessDates?

[xlnitwit]

Yes, sorry. It was typed freehand here so I confused myself!

[dmcgov]

so i got the stub to work, thanks for that.

now i have to create the macro that moves the data from the workbook "Status of POs" on the "Closed POs" sheet to the "ClosedPOsOverAMonth" workbook on the "ClosedPOs" sheet. in fact there will only be one sheet in this workbook so best to refer to this as Sheets(1) i think.

the first thing is to see if the "ClosedPOsOverAMonth.xlsm" is Open, if it's not then open it (my code dies at this part), note that i removed the if statement to get the worksheet to open but it still fails.

then when the date changes in col D on the "Closed POs" sheet, it moves the row (A:D) to the next available row in "ClosedPOsOverAMonth". there is only one table on this sheet.

so here is the code that is failing:

Please Login or Register  to view this content.

[dmcgov]

im stuck on the wb and ws statements. it errors on the set tbl=ws.listobjects(1) line.

any ideas on how to fix this error?

[dmcgov]

so i had to start over from the stub. i got this working (sortof), my question is how to move the target.row and add that to the table on ClosedPOs sheet?

Please Login or Register  to view this content.

[dmcgov]

this is what i have so far but its not copying the range, can someone take a look and see what the issue is?

see the with newrow section for what is not working.

Please Login or Register  to view this content.

[dmcgov]

so i got this to work, like so:

Please Login or Register  to view this content.

thanks xlnitwit for pointing me in the right direction.