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

[cdurfey]

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

[Excel Fox]

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?

[cdurfey]

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

[Excel Fox]

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.


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

[cdurfey]

I am sorry but I cannot get this macro to save. Can you please let me know how to do that? Thanks

[Excel Fox]

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.

[cdurfey]

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

[Excel Fox]

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?