VBA Copy / Paste of row

[Nuke_It_Newport]

Hi everyone-

I have a workbook with 2 sheets, "Sheet 1" and "Clipboard". I need to do the following when the Workbook_SheetSelectionChange event is fired:

• Change the active cell's background color to one color, change the background color of the active cell's row to a different color to "highlight" the active cell and row. See screenshot.
• Remove the formatting from the previously selected cell and associated row. Copy the row from the "Clipboard" worksheet and paste in the row that was previously selected.
• Copy the active cell's row to a different worksheet named "Clipboard".

I have the following code in the Workbook_SheetSelectionChange module:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Dim startR As Integer 'First row after table header row
Dim lCol As Long    'Last column in table
Dim rng As Range
Dim colLetter As String 'Column letter
Dim wb As Workbook
Dim ws As Worksheet
Dim listObj As ListObject

Static prevRange As Range

Set wb = ThisWorkbook
Set ws = wb.ActiveSheet
Set listObj = ws.ListObjects(1)
Set rng = listObj.Range
startR = 5
lCol = rng.Columns(rng.Columns.Count).Column
colLetter = Split(Cells(1, lCol).Address, "$")(1)

On Error Resume Next

If ActiveCell.Row >= startR Then
If Target.Count > lCol Then Exit Sub

'Copy row from "Clipboard" worksheet, paste into previously selected row
With Sheets("Clipboard").Range("A" & startR, , lCol & startR)
    .Copy ActiveSheet.Range("A" & prevRange.Row, lCol & prevRange.Row)
End With

'Remove formatting, set formatting on Active Row
With ActiveSheet.Range("A" & ActiveCell.Row, colLetter & ActiveCell.Row)
     .FormatConditions.Delete
     .SetFirstPriority
     .Interior.Color = 10092543     'Light Yellow
 End With

'Set formatting on Active Cell
With Range(Target.Address)
    .SetFirstPriority
    .Interior.Color = 15921906     'Light Gray
End With

'Copy Active Row to "Clipboard" sheet before changing selected row
Set prevRange = Target
With ActiveSheet.Range("A" & startR)
    .Copy Sheets("Clipboard").Range("A" & prevRange.Row, lCol & prevRange.Row)
End With
End If

End Sub

The active cell and associated row background colors are changing accordingly, but the active cell's row is not copying to or from the "Clipboard" worksheet. This is the only code in the workbook, so I know there's no other code causing the issue. What am I doing wrong? Is there a better way to accomplish the desired result?

Thanks!

 

[Nuke_It_Newport]

**EDIT**
I found one error. I was referencing lCol instead of ColLetter for the last column to copy / paste. Still no copy or paste...

Option Explicit
Public prevRow As Long

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Dim startR As Integer 'First row after table header row
Dim lCol As Long    'Last column in table
'Dim prevRow As Long
Dim rng As Range
Dim colLetter As String 'Column letter
Dim wb As Workbook
Dim ws As Worksheet
Dim listObj As ListObject

Static prevRange As Range

Set wb = ThisWorkbook
Set ws = wb.ActiveSheet
Set listObj = ws.ListObjects(1)
Set rng = listObj.Range
startR = 5
lCol = rng.Columns(rng.Columns.Count).Column
colLetter = Split(Cells(1, lCol).Address, "$")(1)

On Error Resume Next

If ActiveCell.Row >= startR Then
    If Target.Count > lCol Then Exit Sub
        'Copy row from "Clipboard" worksheet, paste into previously selected row
        With Sheets("Clipboard").Range("A" & startR, , colLetter & startR)
            .Copy ActiveSheet.Range("A" & prevRow, colLetter & prevRow)
        End With
        
        'Remove formatting, set formatting on Active Row
        With ActiveSheet.Range("A" & ActiveCell.Row, colLetter & ActiveCell.Row)
             .FormatConditions.Delete
             .SetFirstPriority
             .Interior.Color = 10092543     'Light Yellow
         End With
        
        'Set formatting on Active Cell
        With Range(Target.Address)
            .SetFirstPriority
            .Interior.Color = 15921906     'Light Gray
        End With
        
        'Copy Active Row to "Clipboard" sheet before changing selected row
        Set prevRange = Target
        prevRow = prevRange.Row
        With ActiveSheet.Range("A" & prevRow)
            .Copy Sheets("Clipboard").Range("A" & startR, lCol & startR)
        End With
End If

End Sub