.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) vs .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0)

[nairion]

I have the following code

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Not Intersect(Target, Range("H:H")) Is Nothing Then
        If Target.Cells.Count > 1 Or IsEmpty(Target) Then GoTo errHandler
        On Error GoTo errHandler
        If Target = "Closed" Then
            Target.EntireRow.Copy Sheets("Closed Projects").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            Target.EntireRow.Delete
        End If
    End If
errHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Instead of searching Column "A" for the last row in "Closed Projects" (as it is an optional field) I want to use Column "C". When I changed the "A" to "C," the code no longer updates on change event....it doesn't copy and paste the row and does not delete the target row, no error messages, no debug.

I've manually triggered the error handler to make sure EnableEvents= True...still no luck. I don't understand much about the intersect/target method so I'm not sure what I am missing.

 

[Joe4]

Well, you are probably getting an error, but the way you set up Error Handling, it is just dumping out of the procedure without giving you any error details.
I think I see where the error is.

It is with this line here (the one you are changing from A to C):

Target.EntireRow.Copy Sheets("Closed Projects").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

This tells it to copy the entire row to your Closed Projects sheet. However, if you move from column A to column C, you do not have enough room to paste the entire row! Whenever copying/pasting the entire row, your paste range MUST start in column A, or you do not have enough room for all the columns.
You can still use column C to find the last row, but you most move 2 columns back to paste in column A. You can do that like this:

Target.EntireRow.Copy Sheets("Closed Projects").Cells(Rows.Count, "[COLOR=#ff0000][B]C[/B][/COLOR]").End(xlUp).Offset(1, [COLOR=#ff0000][B]-2[/B][/COLOR])

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Lastrow As Long
    Lastrow = Sheets("Closed Projects").Cells(Rows.Count, "H").End(xlUp).Row + 1
    On Error GoTo errHandler
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Not Intersect(Target, Range("H:H")) Is Nothing Then
    Dim ans As Long
    ans = Target.Row
        If Target.Cells.Count > 1 Or IsEmpty(Target) Then GoTo errHandler
       
        If Target.Value = "Closed" Then
            Rows(Target.Row).Copy Destination:=Sheets("Closed Projects").Rows(Lastrow)
            Rows(ans).Delete
        End If
    End If
errHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[Joe4]

I think they want to look at column C for their last row on the "Closed Projects" sheet.
If that is the case, they might want to change:

Lastrow = Sheets("Closed Projects").Cells(Rows.Count, "[COLOR=#ff0000][B]H[/B][/COLOR]").End(xlUp).Row + 1

Lastrow = Sheets("Closed Projects").Cells(Rows.Count, "[COLOR=#ff0000][B]C[/B][/COLOR]").End(xlUp).Row + 1

But I think I see where you may be going with this. Since this runs when column H is changed to "Closed", column H should always have a value - at least on new data being moved over. However, I don't know if we can be 100% certain that the existing data on the Closed Project sheet always has a value in column H (a question for the OP to answer). If so, then that should work just fine.

 

[My Aswer Is This]

Yes I see your point also. The original poster did say "C" instead of "A" but I hope he can see what to change if needed or come back here and ask.

I think they want to look at column C for their last row on the "Closed Projects" sheet.
If that is the case, they might want to change:

Lastrow = Sheets("Closed Projects").Cells(Rows.Count, "[COLOR=#ff0000][B]H[/B][/COLOR]").End(xlUp).Row + 1

Lastrow = Sheets("Closed Projects").Cells(Rows.Count, "[COLOR=#ff0000][B]C[/B][/COLOR]").End(xlUp).Row + 1

But I think I see where you may be going with this. Since this runs when column H is changed to "Closed", column H should always have a value - at least on new data being moved over. However, I don't know if we can be 100% certain that the existing data on the Closed Project sheet always has a value in column H (a question for the OP to answer). If so, then that should work just fine.

 

[Joe4]

Yes I see your point also. The original poster did say "C" instead of "A" but I hope he can see what to change if needed or come back here and ask.

Based on how the code was originally written, it might end up being that column H might be even better to use than column C!

 

[My Aswer Is This]

Narion:

On the second line of code where you see this:

Lastrow = Sheets("Closed Projects").Cells(Rows.Count, "H").End(xlUp).Row + 1

Change "H" to what ever column on your sheet will always have data.

 

[nairion]

Thanks guys. I did end up changing it to column H. (Both are required fields, but H eliminates the opportunity for deliberate user error)

To clarify, my attempt to modify last row code found the last row in Column C and moved down 1 cell, when it should have moved down 1 cell and back 2 before pasting, whereas the code provided by MyAnswer finds the row value and adds 1, then pastes at the beginning of that row?

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

Thanks guys. I did end up changing it to column H. (Both are required fields, but H eliminates the opportunity for deliberate user error)

To clarify, my attempt to modify last row code found the last row in Column C and moved down 1 cell, when it should have moved down 1 cell and back 2 before pasting, whereas the code provided by MyAnswer finds the row value and adds 1, then pastes at the beginning of that row?