Conflicting Code (Add Row & Move Row to another Worksheet)

[mykal66]

Hi

I use a couple of bits of code on various workbooks but when i try to combine I can't get the result I want.

To make workbooks neater i add a greyed out row at the bottom of entered data then add a cmd button into the greyed out cell in Column A and some code that adds a new row above with formatting etc whenever the user needs a new row which works fine on it's own

The second piece of code moves a row of data to another worksheet when a specific status is set in a column and deletes the original row and this works fine on it own too.

It's when i try to combine both in one workbook i get stuck because when the row is moved and deleted the cmd button moves our of the greyed out cell so when the user click the add new row button it add below the greyed out row.

Is there a way to fix the cmd button into the greyed out row no matter where it moves?

Thanks for any pointers and i have attached an example workbook

Mykal2739927399

[snb]

This code suffices:


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Target.Column = 21 And Target.Value = "Completed" Then Target.EntireRow.Cut Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1)

Application.EnableEvents = True
End Sub

[mykal66]

HI snb, thanks for your help. I have tried to incorporate it into my workbook but still can't get it to work?

[snb]

You should replace your code by mine: ergo remove all your own code first.

[mykal66]

Hi again. Sorry this hasn't worked either. It does move the row to the completed tab but leaves the then blank row in the original tab. I tried to use the code in mine that deletes the row and moves everything up but the CMD button then displaces out of the grey shaded row and when used add row below the shaded line rather than above.

My original code did everything apart from fixing the cmd button into the shaded row when blank rows were deleted and moved up

[snb]

You can't delete rows in a worksheet: the number of rows is constant: rows.count
You can simply sort data in order to move empty rows down.