Copy list contents down, then clear contents

[hektisk]

I have a table with drop down boxes/lists and formulas in certain columns which I'd like to copy down when a new row is added to the table. For whatever reason, Excel will not copy down these formulas and lists on a consistent basis. As a result, I've turned to VBA to help address this issue. I'm using the following code, which is modified from code provided to me for another worksheet by jaslake:

Please Login or Register  to view this content.

This does exactly as it was intended to do on the other worksheet - it copies the contents from the row prior down to the new row. The problem is, unlike the worksheet for which this code was originally written, in which it was all formulas being copied down, this worksheet has lists/drop-downs being copied down, and as a result it is taking the actual value from the cell above and copying it into the new cell.

1) Is there a way to copy only the drop-down list from the cell above, and not the value?
2) If not, how would I modify the above code to clear the value of each cell after the drop-down list and value are copied down, without clearing the drop-down box?

Thank you in advance.

Adam

[jaslake]

Hi Adam
I remember your project well...we struggled with that for a while didn't we.

I don't recall the specific code you referenced but it seems to me you could do something like this after you've done your copy/paste

Please Login or Register  to view this content.

This is not tested. You could probably put this in one line of code if you can wrap your head around it.

[hektisk]

Thank you as always!

[jaslake]

Glad to help. You're welcome.