Need HELP with code auto move whole row to new worksheet based on data

crazydoglady

New Member
Joined
Feb 10, 2018
Messages
10
hi i could really use some help. i have two work sheets outstanding and and i want to be able to automatically hide an entire row based on the text "closed" from my outstanding worksheet and move it to completed.
i know this cant be done with a function or formula and my knowledge of code is awful.
Can someone please help?
my closed column is a drop down not sure if it makes a difference and my rows start in A3

Outstanding
ABCDEFG
1234anybroken downbelfastanyclosed1/1/18
3858anybroken downnewyorkanyclosed2/2/18

<tbody>
</tbody>
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
When you say "move it to completed" do you want to delete that row in the "Outstanding" sheet or do you want to hide that row?
 
Upvote 0
Try this on a copy of your file. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make selection in column F.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target = "closed" Then
        Target.EntireRow.Copy Sheets("completed").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        Target.EntireRow.Delete
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Try this on a copy of your file. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make selection in column F.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target = "closed" Then
        Target.EntireRow.Copy Sheets("completed").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        Target.EntireRow.Delete
    End If
    Application.EnableEvents = True



Hi i don,t know what i am doing wrong but i cant get it to work,
perhaps if i was to upload my worksheet you might be able to figure out what i am doing wrong? :)
 
Upvote 0
You cannot post attachments on this Forum. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.
 
Upvote 0
Try this on a copy of your file. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make selection in column F.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target = "closed" Then
        Target.EntireRow.Copy Sheets("completed").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        Target.EntireRow.Delete
    End If
    Application.EnableEvents = True


hi mumps.. its ok all good i got it working perfect it was a capital letter. thanks for your help this has been driving me crazy. i am going to have to learn more about code. thanks you're brilliant :)
 
Upvote 0
hi mumps.. its ok all good i got it working perfect it was a capital letter. thanks for your help this has been driving me crazy. i am going to have to learn more about code. thanks you're brilliant
:)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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