Assign next available number

[Jean]

I have a worksheet(Sheet2)with sequentially numbered
drawing #'s and descriptions. I need to have another
worksheet(Sheet1) where it displays the next available
number and you key in the description, then puts that
number and description on (Sheet2)worksheet on the next
line and then clears the description on the(Sheet1)
worksheet and shows the new next available number. I don't
know where to begin, any help would be appreciated.

 

[Dick Kusleika]

Jean

Are the drawing numbers anything fancy, or just 1,2,3,etc?

On sheet2, use a formula for the next drawing number. Assume your sheet1
number and descriptions are in columns A and B respectively.

=MAX(Sheet1!A:A)+1

Then put a button on sheet2 that runs this macro. Assume that formula is in
A1 and the description will be in B1

Sub MakeNewDrawing

If Not IsEmpty(Sheet2.Range("B1")) Then
With Sheet1.Range("A65536").End(xlUp).Offset(1,0)
.Value = Sheet2.Range("A1").Value
.Offset(0,1).Value = Sheet2.Range("B1").Value
End With
Sheet2.Range("B1").ClearContents
Else
MsgBox "You must enter a description"
End If

End Sub

Now your formula will show the next number and the description will be
empty.

If you have fancy numbers like 04-001, 04-002, etc. you'll need a fancier
formula, but the macro should be the same.