Code no longer working
 

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
--
Traa Dy Liooar

Jock

Code no longer working
 

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
--
HTH...

Jim Thomlinson


"Jock" wrote:

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
--
Traa Dy Liooar

Jock

Code no longer working
 

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

Jock


"Jim Thomlinson" wrote:

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
--
HTH...

Jim Thomlinson


"Jock" wrote:

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
--
Traa Dy Liooar

Jock