Want to auto populate a row from one sheet to another when a form button is pressed

Hello,

(excel 2007)
I am looking to use a button to auto populate a range of cells from the respective row to the bottom of a table on another sheet and then delete that row on the original sheet. For example, on sheet two I have a button in cell J2. When I click it, I want it to auto populate A2-I2 to the last row of my table on sheet 3 and delete A2-J2 from sheet 2. I tried recording a simple copy and paste macro using relative reference, but it doesn't seem to work. Also not sure if an active x or form button is the best to use in this scenario. The purpose of this workbook is tracking the progress of people through a submittal system. So sheet 1 is people pending submittal, sheet 2 is people pending qualification, and sheet 3 is to track those who have been qualified for 2 years. Is this possible?

Regarding the buttons, there is a newer and much simpler way - using tick marks.
See this thread - http://www.excelforum.com/july-compe...move-tick.html
You can insert the tick marks using the double click and then have a single button for the movement of rows.

Or, you can also have a tick mark inserted this way -
Select column J and change the font to webdings. Whenever you type in "a", in any cell in that column, it will change to a tick mark. Then you can have 1 single button which will check for a tick mark in column J and proceed with moving the rows.

Let me know which approach you prefer.

arlu1201,

Thanks for the reply and the idea. I have decided to use the webdings way so that i can use an IF statement to autopop column J with a tick based on criteria in other cells. That is working wonderfully, however I am having some trouble with the macro to loop through the rows of column J, identify those that are checked, and paste them into the next sheet. Here is the code I am using.

Please Login or Register  to view this content.

This is identifying the row with a tick okay, but when it goes to paste into the other sheet I get a "400" error. Also, not sure how to make this code find and copy multiple rows at once, so that I don't have to repeatedly click the button until all the desired rows are copied. Any ideas?

Edit:

Btw, I have also tried this code, recommended by another use in another thread. It doesn't seem to do anything at all.

Please Login or Register  to view this content.

Try this code

Please Login or Register  to view this content.

Put it in a standard module.

Am glad it worked.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save