Moving data from page to page with a button

topher1166

New Member
Joined
Mar 3, 2011
Messages
31
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.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Which column is the "certain cell" in"

This could be made automatic as soon as completed is entered.
 
Upvote 0
Try this: right click the sheet tab, select View Code and paste in

Rich (BB code):
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.
 
Upvote 0
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?
 
Upvote 0
try this

Code:
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
 
Upvote 0
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
 
Upvote 0
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

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
 
Upvote 0
Is there data in column A? If not, which is the first column containing data?
 
Upvote 0

Forum statistics

Threads
1,222,142
Messages
6,164,167
Members
451,880
Latest member
2da

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top