Macro needed to copy rows from sheet1 and paste to sheet2 if date in colmn h is >today

[Rekoons38]

Brand new green guy seeking help with this code. I have seen variants posted but can't seem to make any work for my situation. I will try to upload an example of my worksheet. What I am looking for is to keep a list of fleet vehicles (column A) on sheet one. this will hold real time data as work flows through the shop. As dates are entered into "Date completed" column "H", I need to have that row pasted into the next available row on sheet 2(after row1, where I will need to have my header).

Sheet 2 will keep a running history of repairs to be referenced by access later.

With some of the code I have seen you guys write this seems like an easy one, but it has so far illuded me! Please help.

Thanks,
Rekoons38

[bmxfreedom]

Instead of a macro that copies and pastes, you can use formulas to accomplish this. I will build an example and post it shortly.

[stnkynts]

Put this in sheet 1: (Triggers when you put a date in column H)

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Does this help?

Please Login or Register  to view this content.

[bmxfreedom]

Here's the file I talked about. The formulas that do compile this are all contained in sheet2. They are a combination of MATCH and INDIRECT, with some use of ADDRESS(), ROW(), and COLUMN(), that simply make use of existing positions withing the sheet so that formulas can be identical and yield different results. You can take this a step further by referring to the values in column C indirectly like this "INDIRECT("C"&ROW())", which would make the formulas all identical even beyond each row, but I didn't bother with this option. The formula rows would need to be extended (just copy a row and paste it down as far as needed) beyond the 200 I've created if the number of completed listings ever grows beyond 200.
This is a good option if you need to have a non-macro enabled environment or you have users who don't understand how to enable macros, etc.
I hope this helps you out.

[Rekoons38]

John, what would keep the macro from showing after save. It simply is not showing?? I have save a nd ran a few tests, so this is not making sense.

[JOHN H. DAVIS]

Did you place the code in the Sheet Events Module?

[bmxfreedom]

You need to save as a .xlsm format. (macro-enabled format).

[Rekoons38]

bmx, that is some impressive formula writting! However it is overwriting the data the next time I change information on sheet 1. I really need it to add to the next empty row on sheet 2 to keep repair history.

[Rekoons38]

Saved the file as .xlsm. Alt F11 and paste into VBA editor, alt F11, alt F8 and not listed.

[bmxfreedom]

bmx, that is some impressive formula writting! However it is overwriting the data the next time I change information on sheet 1. I really need it to add to the next empty row on sheet 2 to keep repair history.

It doesn't overwrite, it just moves. Those formulas just compile the existing list in its current order. If you need something that compiles in date order, that can be done via formulas as well, but it gets even more complicated. Compilation via change order can only be accomplished with VBA, so I suspect the VBA solution provided would be superior here.

[Rekoons38]

Ok, I have copied the code, opened the vba editor and pasted the code in. It automatically changes the list boxes on top of the editor to "worksheet" instead of general on the left and on the right "Change". I should be able to go to alt f8 and view it at that time correct?

[Rekoons38]

[Rekoons38]

Don't know what I was doing wrong, but finally made John's work!! Thanks everyone for the help!!

[JOHN H. DAVIS]

You're welcome. Glad to help out, and thanks for the feedback.