Moving data from page to page with a button

[topher1166]

I'm having trouble trying to accomplish a task in a work book I have created.

I have a work book containing a few pages. I'm trying to move a certain "file" (one whole row containing 11 columns of data) out of the current page into an "archive" section on another page IF a certain cell = "complete". This would be triggered with a button/command button.

So what I really want to do is clear the cells and move the content to another sheet. Is this possible? If so, what whould happen to any formuals involved in a move?

Thanks!

Any help would be greatly appreciated.

 

[VoG]

Which column is the "certain cell" in"

This could be made automatic as soon as completed is entered.

 

[topher1166]

The column that has complete in it is "g".

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 And LCase(Target.Value) = "completed" Then
    Application.EnableEvents = False
    Target.EntireRow.Copy
    Sheets("Archive").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    Target.EntireRow.Delete
    Application.EnableEvents = True
End If
End Sub

Change Archive to the name of the archive sheet. Press ALT + Q to exit the code window. Enter completed in column G to transfer the row.

 

[topher1166]

That certainly does clear the cells and move them to the correct page, awsome!... BUT if you "complete" multilpe entrys they replace each other on the archive page (only on file shows up on the archive page no matter the number completed)

Any ideas?

 

[VoG]

try this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
For Each c In Target
    If c.Column = 7 And LCase(c.Value) = "completed" Then
        Application.EnableEvents = False
        c.EntireRow.Copy
        Sheets("Archive").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
        c.EntireRow.Delete
        Application.EnableEvents = True
    End If
Next c
End Sub

 

[topher1166]

I replaced the original text with the new text. The new text doesn't seem to work. when I make column g "completed" (data validation drop down) nothing happens.

any ideas?

either a button or automatically would work

 

[VoG]

Events may be disabled. In the code window press CTRL + G to open the Immediate Window and in that window enter

Application.EnableEvents=True

and press Enter.

Then try this modified code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, r As Range
Application.EnableEvents = False
For Each c In Target
    If c.Column = 7 And LCase(c.Value) = "completed" Then
        If r Is Nothing Then
            Set r = c
        Else
            Set r = Union(r, c)
        End If
        c.EntireRow.Copy
        Sheets("Archive").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    End If
Next c
If Not r Is Nothing Then r.EntireRow.Delete
Application.EnableEvents = True
End Sub

 

[topher1166]

After doing that the cells move to the correct sheet but they're still replcaing each other on the new sheet

 

[VoG]

Is there data in column A? If not, which is the first column containing data?