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.

Public Sub CopyRows()
    Sheets("Pending Stamp").Activate
    ' Find the last row of data
    FinalRow = Range("A65536").End(xlUp).Row
    ' Loop through each row
    For X = 2 To FinalRow
    
    ThisValue = Range("J" & X).Value
    If ThisValue = "a" Then
            Range("A" & X & ":AG" & X).Copy
            Sheets("2YR Hold").Activate
            NextRow = Range("A65536").End(xlUp).Row + 1
            Range("A" & NextRow).Select
            ActiveSheet.Paste
        End If
    Next X
End Sub

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.

Public Sub CopyRows()
    ' Find the last row of data
    FinalRow = Sheets("Pending Stamp").Range("A65536").End(xlUp).Row
    ' Loop through each row
    For Each c in Sheets("Pending Stamp").Range("G" & FinalRow)
         If c.Value = "a" Then
              Sheets("2YR Hold").Range("A65536").End(xlUp).Row.Offset(1.0).Value = c.Value
         End If
    Next

End Sub

Try this code

Option Explicit

Sub copy_rows()
Dim i As Long, lrow As Long

With Worksheets("Pending Stamp")
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    For i = 2 To lrow
        If .Range("J" & i).Value = "a" Then
            .Range("A" & i & ":I" & i).Copy Worksheets("2YR Hold").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        End If
    Next i
End With
End Sub

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