Hope I've understood the requirement.

One solution.

Create a single sheet workbook called "InvoiceNumber.xls" and name
cell A1 "InvNo"
The only purpose of this workbook is to hold the next number. Save it.

In your workbook 2002 add a "Variables" sheet - (you may already have
one which holds other variables), and again name a cell "InvNo"

When you say the user clicks a hyperlinked cell I wasn't quite sure
what you meant, since a hyperlink would just take you somewhere else.
In this solution create a Command Button on each of the discrepancies
sheet in your Wb and set the caption property name to "Add Invoice
Number".

In the Click event of the Button View code VBA window enter the
following:

Private Sub CommandButton1_Click()
Call AddInvoice.OpenInvWb
End Sub

Add a module and name it "AddInvoice"
Now copy the procedure below into the AddInvoice module

When the usre clicks the Command button, it opens the "InvoiceNumber"
workbook, sets the iInvNo variable to the current Inv No, increments
the Invoice No in the InvoiceNumber workbook and re-saves it.

It then puts the iInvNo variable into the "InvNo" cell in the 2002
workbook.

It then confirms with the user via a Message box that they really do
want to add the new number, and if they decline it re-opens the
InvoiceNumber workbook and decreases the InvNo by 1.

If they accept, the number will be copied to the next available blank
cell in Column B

Sub OpenInvWb()
Dim TempWb As Workbook, iInvNo As Integer, iAnswer As Integer
Application.ScreenUpdating = False
Workbooks.Open ("InvoiceNumber")
Set TempWb = ActiveWorkbook
Range("Invno") = Range("Invno") + 1
iInvNo = Range("invno")
TempWb.Save: TempWb.Close
Range("Invno") = iInvNo

iAnswer = MsgBox("You are about to add Invoice Number " _
& Range("Invoicenumber") & Chr(13) _
& " Is this OK?", vbYesNo)
If iAnswer = 7 Then
Workbooks.Open ("Invoicenumber")
Set TempWb = ActiveWorkbook
Range("Invno") = Range("Invno") - 1
iInvNo = Range("invno")
TempWb.Save: TempWb.Close
Range("Invno") = iInvNo
Exit Sub
End If
ActiveSheet.Range("B65536").End(xlUp).Offset(1, 0) =
Range("invno")
Application.ScreenUpdating = True

End Sub


As an alternative to putting Command Buttons on all of your various
sheets, and particularly if you have a lot, or you have other tasks
that could sensibly be done via a menu, it would be preferable to set
up a user form "MyForm" containing a command button and display the
userform with a procedure that's called by a macro shortcut - say
CTRL-A for "Add" - which points to the "DisplayForm" procedure.

Sub DisplayForm
Myform.Load
End Sub


HTH.
Re-post if it's not clear.




On Fri, 25 Aug 2006 11:25:02 -0700, Larry
wrote:

Hi Richard, thanks for the reply.
the cell I wnat to have the new number in is always the next cell in the
column. i.e.
Driver opens the workbook 2002 and selects sheet 02b2000 which corresponds
to a certain vehicle. column A is the dynamic list for descrepencies; user
can select from the drop down or write their own descrep, shich will be added
to the bottom of the list. column B is where the new, unique number goes for
that descrep. column C is the date which autofills on selection and column D
is for the driver initials taken from a static validation list.
So, the trick is to get the nex empty cell in column B"waiver no's" to :
1- Generate the next highest number for all workbooks and sheets so it is
unique and not used again.
2-
go and get the next new number and place it in the next empty selected cell.

Is this possible to do automatically so drivers won't have to go to the new
number workbook and cut and paste? thanks richard.


"Richard Buttrey" wrote:

How are you defining the target cell in the second workbook?

Is it a named cell, or a cell which has a varying position, e.g. the
next blank cell in a column of invoice numbers?

It's not immediately apparent why you need this first workbook. Is it
perhaps that it's used to generate the next invoice number, which may
be used by other applications. i.e. the invoice numbers in your
managed fleet workbook are not necessarily being incremented by 1 each
time.

Regards


On Fri, 25 Aug 2006 08:35:28 -0700, Larry
wrote:

Hi folks, I appreciate you being there to help. I am a novice so need lots of
help with this stuff.

I have a workbook that I use to generate a new number each time it is
opened. The number is used to index descrepencies on autos in a managed fleet.
It looks like this in "thisworkbook" :

Private Sub Workbook_Open()
Columns("B:IV").Select
Selection.EntireColumn.Hidden = True
Range("A65536").End(xlUp).Offset(1, 0).Select
Selection.Value = Selection.Offset(-1, 0).Value + 1
MsgBox "To copy this number hold the Ctrl and C keys together. The new
number box buzzes. Don't close this page yet. Click on the waiver card tab at
the bottom of the window to open it again. Hold down the Ctrl and V keys
together, the new number will be entered in the new waiver block you had
selected. Now you can go to bottom of the window and right click the New
Number page and select close: NOTE:You have to close this before starting!"
ThisWorkbook.Save
End Sub


The user: drivers, click a hyperlinked cell on a sheet designated for a
vehicle, then copy and paste the new generated numberfrom the opened workbook
into the target cell in a different workbook.
Oh how I wish there was a way to have the active workbook target cell just
enter the new number when it is clicked. No more cut and paste which confuses
some of the users. Any takers on this one? thanks, larry

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________