Code no longer working

  • Thread starter Thread starter Jock
  • Start date Start date
J

Jock

Hi, can someone look at the following code, which copies certain data from
sheet 'claims' to sheet 'charging', and suggest why it doesn't work? It is in
a module and worked ok until recently so I am wondering if code elsewhere in
the workbook is affecting it somehow. I get the error message "Compile error:
Argument not optional" with 'Last Row' (line 5) highlighted:

Sub Charge()

If ActiveCell.Value = "C" Then
RowNo = ActiveCell.Row
With Sheets("Charging")
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
Newrow = LastRow + 1
End With
Sheets("Charging").Range("B" & Newrow).Value = _
Sheets("claims").Range("D" & RowNo)
Sheets("Charging").Range("C" & Newrow).Value = _
Sheets("claims").Range("E" & RowNo)
Sheets("Charging").Range("D" & Newrow).Value = _
Sheets("claims").Range("F" & RowNo)
Sheets("Charging").Range("E" & Newrow).Value = _
Sheets("claims").Range("G" & RowNo)
Sheets("Charging").Range("F" & Newrow).Value = "14.50"
Sheets("claims").Activate
Application.Goto Reference:=Worksheets("Claims").Range("F" & Newrow)

End If

End Sub

Thanks for your suggestions
 
That code compiles so that is not our issue. My best guess is that you have
declared a funciton or sub somewhere called last row. Try this change to see
if it starts working...

Sub Charge()
Dim lngLastRow As Long
Dim lngNewRow As Long
Dim lngRowNo As Long

If ActiveCell.Value = "C" Then
lngRowNo = ActiveCell.Row
With Sheets("Charging")
lngLastRow = .Range("B" & Rows.Count).End(xlUp).Row
lngNewRow = lngLastRow + 1

.Range("B" & lngNewRow).Value = _
Sheets("claims").Range("D" & lngRowNo)
.Range("C" & lngNewRow).Value = _
Sheets("claims").Range("E" & lngRowNo)
.Range("D" & lngNewRow).Value = _
Sheets("claims").Range("F" & lngRowNo)
.Range("E" & lngNewRow).Value = _
Sheets("claims").Range("G" & lngRowNo)
.Range("F" & lngNewRow).Value = "14.50"
Sheets("claims").Activate
Application.Goto Reference:=Worksheets("Claims").Range("F" & lngNewRow)
End With
End If

End Sub
 
Indeed I do - there's a Function called LastRow in a Module. I will
investigate in the morning!
Thanks Jim.
 
Back
Top