VBA: Simple Copy and Paste using Cells

KStecyk 

Try this:

Sub macro2()
     
     If ActiveSheet.Name <> Sheet1.Name Then
          Exit Sub
     End If
     
     With Sheet1.ListObjects("Table1")
          If Not Intersect(ActiveCell, .DataBodyRange) Is Nothing Then
               With Intersect(ActiveCell.EntireRow, .DataBodyRange)
                    Sheet1.Range("A20").Resize(1, .Columns.Count).Value = .Value
               End With
          End If
     End With
     
End Sub

That line of HardCodeB does not work because it is expecting a reference in the format rnKeyRange(Row, Col) and you are giving it range references. For example, rnKeyRange(1,1) is a reference to cell "A2" (the top left cell of the rnKeyRange "A2:H16").

Range references are relative to your starting range. Since your lRowValue is not relative to the Range rnKeyRange, but is relative to the worksheet, you should be able to use

Sheet1.Range(Sheet1.Cells(lRowValue, 1), Sheet1.Cells(lRowValue, 8)).Copy

or

With rnKeyRange.Worksheet
.Range(.Cells(lRowValue, 1), .Cells(lRowValue, 8)).Copy
End With

It is a generally good practice to qualify your Cells reference for situations in which your macro may not be running on the active sheet. For example, Sheet1.Range(Cells(1,1), Cells(1,10)) - the Range object refers to Sheet1, but the Cells objects still refer to the active sheet. Not really necessary in this case since I'm testing to ensure the activesheet is Sheet1 before proceeding, but I do it anyway.