Macros to Update date and invoice number

[Dolphinv4]

Hi,

I created a macro to save a workbook as a new book
called "NEW". This is easily done by recording a macro,
however, I want the following functions after i save the
new book which I do not know the code:

1) Cell A1 in Sheet1 is to be equals to Cell A1 PLUS 1
in the sheet before "SheetSUP". I will be adding new
sheets before the "SheetSUP" constantly thus I cannot use
the name of the sheet before "SheetSUP" in the code...ie,

the sheets are all invoices thus A1 have running numbers.
For every new month, I open an old book and saveas a new
book. Subsequently, the macro is to copy the invoice
number from the last invoice (sheet before "SheetSUP")
and then plus 1 and put it in cell A1 in the 1st invoice
which is Sheet1. What is the code I should use?


2) The invoices are dated 1st of every month. So when I
copy the old book and save as the new book, it'll be the
1st of the last month. What code should I use to change
the date to the 1st of the next month?

Thanks & Regards,
Val

 

[papou]

Hello Val
Here's a try for #1
Sub test()
On Error GoTo NotFound
Dim i&, ShtName$
With ThisWorkbook.Worksheets
For i = 1 To .Count
If .Item(i).Name = "SHEETUP" Then
ShtName = .Item(i - 1).Name
Exit For
End If
Next i
End With
ThisWorkbook.Worksheets("Sheet1") _
..Range("a1").Value = _
ThisWorkbook.Worksheets(ShtName).[a1] + 1
Exit Sub
NotFound:
MsgBox "There is either no worksheet before SHEETUP" _
& " or Sheet1 has not been found", vbInformation, "Not found"
Exit Sub
End Sub

As regards #2, if the date of your invoice is on you worksheet (say in A2),
then simply use formulae:
=DATE(YEAR(A2),MONTH(A2)+1,DAY(A2))

HTH
Cordially
Pascal