Finding next available cell

[TourGuy696]

I received this code from a VBA forum that mostly deals with VBA for
PC users... when I try to run it in Excel X I get a syntax error in
the first line....

Does anyone know what the problem might be?
The point of the code is to enter data in the next available cell on a
sheet called Records from a sheet called data.

Thanks!
-----

Sub KeepRecords()
    Dim LastRow As Object
        Set LastRow = Sheets("Records").Range("A65536").End(xlUp)
    
    With LastRow
        .Offset(1, 0) = Sheets("Data").Range("A1")
        .Offset(1, 1) = Sheets("Data").Range("B2")
        .Offset(1, 2) = Sheets("Data").Range("C3")
        .Offset(1, 3) = Sheets("Data").Range("D4")
        .Offset(1, 4) = Sheets("Data").Range("E5")
    End With
    
End Sub

 

[JE McGimpsey]

From the characters in the rows after "Sub" I think you probably have
non-printing characters prefixing your code lines. This can happen if
you paste the code in from another source. Try replacing them with
spaces.

I would do it a bit differently:

Public Sub KeepRecords()
Dim rData As Range
Dim i As Long
Set rData = Sheets("Data").Range("A1")
With Sheets("Records").Cells(Rows.Count, 1).End(xlUp)(2, 1)
For i = 1 To 5
.Item(1, i).Value = rData(i, i).Value
Next i
End With
End Sub

 

[Guest]

Thanks! That worked like a charm

-----Original Message-----
From the characters in the rows after "Sub" I think you probably have
non-printing characters prefixing your code lines. This can happen if
you paste the code in from another source. Try replacing them with
spaces.

I would do it a bit differently:

Public Sub KeepRecords()
Dim rData As Range
Dim i As Long
Set rData = Sheets("Data").Range("A1")
With Sheets("Records").Cells(Rows.Count, 1).End(xlUp)(2, 1)
For i = 1 To 5
.Item(1, i).Value = rData(i, i).Value
Next i
End With
End Sub


.