Page 1 of 2 12 LastLast
Results 1 to 10 of 15

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

  1. #1
    New Member
    Join Date
    Feb 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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
    A B C D E F G
    1234 any broken down belfast any closed 1/1/18
    3858 any broken down newyork any closed 2/2/18

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,243
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

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

    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?
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    New Member
    Join Date
    Feb 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by mumps View Post
    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?

    Hi sorry had to think about that one. i just want to move it to completed

  4. #4
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,243
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

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

    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
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  5. #5
    New Member
    Join Date
    Feb 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    [QUOTE=mumps;5006607]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?

  6. #6
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,243
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

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

    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.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  7. #7
    New Member
    Join Date
    Feb 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: Need HELP with code auto move whole row to new worksheet based on data

    [QUOTE=crazydoglady;5006629][QUOTE=mumps;5006607]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

  8. #8
    New Member
    Join Date
    Feb 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  9. #9
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,243
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

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

    You are very welcome.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  10. #10
    New Member
    Join Date
    Feb 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by mumps View Post
    You are very welcome.
    you may have started something mumps lol you might just be might go to person lol

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •