Generate Auto Number

[Maninder Karir]

Hi

Im stuck AGAIN!!!!!! Sure its something simple but, Ive put together a code to generate an automatic number everytime i open the work book, However I now would rather only generate the number when I click on a particular button.

The code works fine when its in a Private Sub Auto Open(). ive copied the code directly into a command button code. Upon clicking it selects the right sheet but does not generate the new number.

This is what i Got

Private Sub ReqBut_Click()

Sheets("Orders Sent").Select

For Row = 2 To 64000
If Cells(Row, 1) = "" Then
GoTo RowSkip1
End If
Next Row

RowSkip1:

LastRow = Row - 1
Currentrow = Row
If LastRow < 10 Then
Cells(Currentrow, 1).Value = "AL" & 0 & 0 & 0 & LastRow
End If
If LastRow > 9 And LastRow < 100 Then
Cells(Currentrow, 1).Value = "AL" & 0 & 0 & LastRow
End If
If LastRow > 99 And LastRow < 1000 Then
Cells(Currentrow, 1).Value = "AL" & 0 & LastRow
End If
If LastRow > 1000 Then
Cells(Currentrow, 1).Value = "AL" & LastRow
End If


Worksheets("Chemicals").Range("d2").Value = Cells(Currentrow, 1).Value

End Sub



Any one have any ideas where im going wrong?
Also is there a shorter way of doing what ive done?

Thanks

Manny

[Maninder Karir]

Hi

Im stuck AGAIN!!!!!! Sure its something simple but, Ive put together a code to generate an automatic number everytime i open the work book, However I now would rather only generate the number when I click on a particular button.

The code works fine when its in a Private Sub Auto Open(). ive copied the code directly into a command button code. Upon clicking it selects the right sheet but does not generate the new number.

This is what i Got

Private Sub ReqBut_Click()

Sheets("Orders Sent").Select

For Row = 2 To 64000
If Cells(Row, 1) = "" Then
GoTo RowSkip1
End If
Next Row

RowSkip1:

LastRow = Row - 1
Currentrow = Row
If LastRow < 10 Then
Cells(Currentrow, 1).Value = "AL" & 0 & 0 & 0 & LastRow
End If
If LastRow > 9 And LastRow < 100 Then
Cells(Currentrow, 1).Value = "AL" & 0 & 0 & LastRow
End If
If LastRow > 99 And LastRow < 1000 Then
Cells(Currentrow, 1).Value = "AL" & 0 & LastRow
End If
If LastRow > 1000 Then
Cells(Currentrow, 1).Value = "AL" & LastRow
End If


Worksheets("Chemicals").Range("d2").Value = Cells(Currentrow, 1).Value

End Sub



Any one have any ideas where im going wrong?
Also is there a shorter way of doing what ive done?

Thanks

Manny

Just something ive noticed,
The cells which i want updating dont show the number (on the Order Sheet)
but when i press the command button the cell d2 on the chemical sheet does change and increment as it should.