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
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
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
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
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
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
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
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
Posting Permissions
Reply With Quote
Bookmarks