Results 1 to 8 of 8

Thread: VBA Code To Move A Row To A Different Worksheet Within The Same Workbook

  1. #1
    Junior Member
    Join Date
    May 2013
    Posts
    21
    Rep Power
    0

    VBA Code To Move A Row To A Different Worksheet Within The Same Workbook

    I am very new to VBA and need some help. I need to move an entire row from a worksheet called Master to a worksheet called Completed in the same workbook on certain conditions. If Y is selected from the drop down in column I then the entire row needs to be moved to the next available row on the Completed tab. I need this to happen automatically. I am not sure where to even start. Any help will be appreciated. Thanks

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    cdurfey, welcome to ExcelFox.

    So this row, does it contain just values? Or does it contain some formula also? if it does contain formula, when you copy it to the completed tab, should it be paste as values or should the formula remain? And what about other formats like color, font etc?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    May 2013
    Posts
    21
    Rep Power
    0
    It contains formulas that need to be copied over as well. There is a conditional format that changes the color of the row also that needs to be copied over. Thanks
    Last edited by Excel Fox; 05-17-2013 at 08:49 PM. Reason: Removed Quotes

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Copy this to the sheet module of the sheet from which you want to cut the row. And change the destination sheet name as required.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        If Target.Cells.Count = 1 And Target.Column = 9 And Target.Cells(1).Value = "Y" Then
            Target.EntireRow.Copy Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)(2)
            Target.EntireRow.Delete xlUp
        End If
        
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Junior Member
    Join Date
    May 2013
    Posts
    21
    Rep Power
    0
    I am sorry but I cannot get this macro to save. Can you please let me know how to do that? Thanks
    Last edited by Excel Fox; 05-17-2013 at 09:58 PM. Reason: Quote Removed

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Why wouldn't you be able to save? Just right-click on the sheet from which you want to copy, and paste the above code in the code window for that sheet. Then save the file as a macro enabled file. If you are using Office 2003 or before, you wouldn't have to worry about the file type either.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  7. #7
    Junior Member
    Join Date
    May 2013
    Posts
    21
    Rep Power
    0
    OK, I got it working but now when I change the cell in column I to "Y" it pops up the folder to open/save. Can I get that to stop? Thanks

  8. #8
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    You probably have your formulas linked to another workbook. Or there's something that links your sheet or workbook to an external file. Can you check?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Replies: 4
    Last Post: 07-02-2013, 11:32 AM
  2. VBA code to move row to new spreadsheet
    By cdurfey in forum Excel Help
    Replies: 6
    Last Post: 06-10-2013, 10:38 PM
  3. Replies: 2
    Last Post: 01-29-2013, 02:45 PM
  4. Vba Code to find value and paste on certain row
    By jwitte in forum Excel Help
    Replies: 3
    Last Post: 11-28-2012, 08:52 PM
  5. VBA Code to Open Workbook and copy data
    By Howardc in forum Excel Help
    Replies: 16
    Last Post: 08-15-2012, 06:58 PM

Tags for this Thread

Posting Permissions

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