Need help, Seq numbers, Save filename & A button on sheet.

[Harv]

I use Excel for my Invoices and most all business info handling.

I use a unique type of Invoice / Job #. It is formatted like this
06-001-01.
The "06" is the year. The "001" is the day of the year (001 - Jan 1st
365 - Dec 31st). The final (2) digits "01" are the Job# for that day
(At this point I only need 2 digits, 01 - 99 jobs per day).

The number generated by the date 01/01/2006 is "06-001" (cell I17)
then I have to change the "01" manually (to other numbers as needed
which is in cell (K17). BTW: (I17 & J17) are merged for a wider cel
space.

When I select the cell that contains the date (B19), then press ( Ctr
& : ) it inserts todays date, then job number/date code ar
automatically generated.
However, the last 2 digits have to be manually changed.


What I want to do is:

1) Have last (2) digits "01", become sequential, automatically, eac
day. (No dup invoice numbers). Each "Save" will increase it by 1. (01
02, 03, etc).

2) Use invoice number as worksheet filename, when saving.

3) Place a "Save" button on the worksheet. (when clicked, it save
using the invoice number.

If anyone can help with any part of this, I would greatly appreciat
it.

I had another issue, but couldn't get an answer for that one. I wil
try that one again, later.

If you like challenges, I have a few more, after these.

Thanks,

Har

 

[Ardus Petus]

see example: http://cjoint.com/?foxZ2HVIXz

1) Done in module1!NextInvoiceNo
Sub NextInvoiceNo()
With Range("A2")
If Date = Range("A1").Value Then
.Value = .Value + 1
Else
Range("A1").Value = Date
.Value = 1
End If
Range("C1").Value = _
Format(Date, "yy") & "-" & _
Format(Date - DateSerial(Year(Date), 1, 0), "000") & "-" & _
Format(.Value, "00")
End With
End Sub

NB:
A1 contains current date
A2 contains current invoice seq no.
Column A is hidden

NextInvoiceNo is called by Workbook_Open and Workbook_Before_Save

2) Done in ThisWorkbook.Before_Save:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Application.EnableEvents = False
Me.Save
Me.SaveCopyAs Range("c1").Value & ".xls"
Application.EnableEvents = True
NextInvoiceNo
Cancel = True
End Sub

3) No need for a button: just save Invoice.xls, and it will be saved under
the proper name.

HTH