Code no longer working

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
 
J

Jim Thomlinson

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
 
J

Jock

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top