How do I add values to cells at the end of a row I just inserted through my VB code

[louvalterry]

I have code that copies and inserts a new row into my audit worksheet when the row is deleted from the original worksheet. Now I want to insert three new bits of data at the end if the just pasted row. The fileds are; Date, Time, and application.user. All the code works but I cannot find the correct code to add the three new fields, Here is my code:

Private Sub cmdDeleteRow_Click()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim LastRow As Long
    Dim rn As Range
    Set rn = ActiveCell.EntireRow
    Dim audit As Worksheet
    Set audit = Worksheets("Audit")
       
    If Not IsInRange(ActiveCell, ActiveSheet.Range("ForecastTable")) Then
        MsgBox "You can not delete lines here.", vbCritical
        Exit Sub
    End If
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect
    ' MsgBox "You are about to copy the deleted data to the Audit tab "
    
    rn.Copy
    Worksheets("Audit").Range("A" & (Worksheets("Audit").UsedRange.Rows.Count + 1)).PasteSpecial xlPasteValuesAndNumberFormats
    rn.Delete
    '
' Code to add date and time stamp and user id
     Worksheets("Audit").Range(0, 77)).Value = Date
     Worksheets("Audit").Range(0, 78).Value = Time
     Worksheets("Audit").Range(0, 79).Value = Application.UserName
' End of code add date and time stamp and user id
    
    ActiveSheet.Protect
    Application.ScreenUpdating = True
End Sub

[p45cal]

RowToPasteTo = Worksheets("Audit").UsedRange.Rows.Count + 1
Worksheets("Audit").Range("A" & RowToPasteTo).PasteSpecial xlPasteValuesAndNumberFormats
Worksheets("Audit").Range(RowToPasteTo, 77).Value = Date
Worksheets("Audit").Range(RowToPasteTo, 78).Value = Time
Worksheets("Audit").Range(RowToPasteTo, 79).Value = Application.UserName

[louvalterry]

p45cal,

Thanks for the code. I incoporated your code into my existing code as shown below. I ran the process in debug and got an error message "Run-time error 1004': Application-defined or object-defined error" on the third line of code. Did I do something wrong?

rn.Copy
    Worksheets("Audit").Range("A" & (Worksheets("Audit").UsedRange.Rows.Count + 1)).PasteSpecial xlPasteValuesAndNumberFormats
    Worksheets("Audit").Range(Worksheets("Audit").UsedRange.Rows.Count + 1, 77).Value = Date
    Worksheets("Audit").Range(Worksheets("Audit").UsedRange.Rows.Count + 1, 78).Value = Time
    Worksheets("Audit").Range(Worksheets("Audit").UsedRange.Rows.Count + 1, 79).Value = Application.UserName
    rn.Delete

[p45cal]

Yes.

Your quoted code in the last message bears no resemblance to mine. Stick to using a variable.

[louvalterry]

p45cal,

Sorry,I get your point, so I cut and pasted your code between my code to copy and then delete in the original code. I add a Dim statement because when I first compiled the code I got a message that "RowToPasteTo" was not defined. It then compiled clean. In Debug I steped through the code and I get the same "Run-time error 1004'..." message on the same line where the vaiable is first used.
Not sure it makes a difference, my version of VBA is 7.0

Dim RowToPasteTo As String

rn.Copy ' my original code
    RowToPasteTo = Worksheets("Audit").UsedRange.Rows.Count + 1
    Worksheets("Audit").Range("A" & RowToPasteTo).PasteSpecial xlPasteValuesAndNumberFormats
    Worksheets("Audit").Range(RowToPasteTo, 77).Value = Date
    Worksheets("Audit").Range(RowToPasteTo, 78).Value = Time
    Worksheets("Audit").Range(RowToPasteTo, 79).Value = Application.UserName
   rn.Delete

' my original code

[Kenneth Hobs]

Dim variables with a row number as Long. Columns can be integers. While you can use CStr() to convert a number to a string, it is generally not needed when using the string concatenation operator, &, with a variable dimmed as a number.

When using "row, column" syntax, use Cells() rather than Range().
e.g.

Msgbox Cells(1, 1).Value
MsgBox Cells(1, "A").Value
MsgBox Range("A1").Value

[p45cal]

Dim RowToPasteTo As Long
or just:
Dim RowToPasteTo

[louvalterry]

P45cal and Kenneth Hobbs

Thanks to both of you for the help you provided. My code now copies successfully from Worksheet "ForeastTable" to "Audit" and then adds a date and Time stamp and the Application Users name who deleted the row. The issue with the code did seem to be that VBA did not like the Range object and once I changed that to Cells it worked fine. All that is left is to clean up the code to remove the msgboxes I was using to debug the code.

Again, the help I received being a novice VBA coder has been a great learning experience and if were not for this form I would be working twice as much and not getting as much done.