Move Row to New Worksheet

**papa francisco** · 05-30-2009, 08:36 AM

I have a worksheet to track projects. I want to move a row to a new worksheet when the project is complete. The completed project is based on wether a column cell has any data.I have attached the spreadsheet. Thanks in advance.

**JBeaucaire** · 05-30-2009, 11:03 AM

Hi Papa, welcome to the forum.

This macro will move all the rows on sheet Project List to the Complete sheet if there is a date in the Actual Completion column. Every time you run it, it will append the rows moved to the bottom of the projects already on the Complete sheet.

Please Login or Register  to view this content.

How to use the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

**papa francisco** · 05-31-2009, 11:29 AM

Thanks JBeaucaire. I have the VBA opened however it appears all of the editing features are transparent. What am I doing wrong? Is the spreadsheet locked out?

**papa francisco** · 05-31-2009, 12:21 PM

I moved everything to a new ws. It wprks if I go in to VBA and manually run the app. How do I make the macro run automatically?

**JBeaucaire** · 05-31-2009, 02:31 PM

It's not an automatic macro. You need to include in your list or requirements the thing you feel should "trigger" the macro if you want it to happen on its own.

I know, you're going to say "When I mark a row complete, it should move"...right? Well, use caution here. You don't think you could accidentally mark the wrong row? Stuff like this where you permanently move completed stuff to another sheet, i REALLY want to suggest you add a button to the top of your sheet so you can just click it to move the rows.

Turn on the FORMS Toolbar.
Click on Button icon and draw a button at the top of the sheet.
The ASSIGN MACRO window will appear automatically
Select the Completions macro you've already put in your sheet.
Now edit the label on the button to "Archive Completions"
Click on a blank part of your sheet to deselect the button

It's not ready to use and will run the macro any time you click it.

Will this be sufficient? That's how I would implement something like this.

**papa francisco** · 05-31-2009, 04:56 PM

Thanks for the response. For grins anf giggles I ran the macro without data in the field and got error 1004. I have a wee bit VB under my belt so I used the on error to jump around the code and end the sub. I figured out how to use keystrokes to start the macro. I'm using ctrl x to initiate the "macro" however the use of a button is cleaner. What I am working on now is trying to figure out how to automatically restore the auto filters automatically. Any thoughts?

**mewingkitty** · 05-31-2009, 05:30 PM

If you're looking to learn more about VBA the best way to do it is just jump in and start flailing around trying to figure out new things. In this case -

restore the auto filters automatically

I'd start with the macro recorder.

Record a macro of you manually doing whatever it is you want to do, then tweak that code, and paste it onto the end of the macro you already have.

**JBeaucaire** · 05-31-2009, 05:43 PM

My apologies, I thought the autofilters were in the original data on accident, so I included a line of code to remove them. In the macro I posted, take out this line:

Please Login or Register  to view this content.

...it's the last line before the end. Remove that.

EDIT:
Or better, yet, change it to this to just show all lines again:

Please Login or Register  to view this content.

That's better.

**papa francisco** · 05-31-2009, 08:54 PM

We have a solution. Thank you for your help.

**JBeaucaire** · 05-31-2009, 09:02 PM

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the "scales" icon in our posts to leave Feedback, it is appreciated)