Forum Discussion

Chamara Wickramasinghe's avatar
Chamara Wickramasinghe
Copper Contributor
Jun 17, 2018

Increase number when saving

I got an Excel template to input Invoice details and save. It has the Invoice Number in one cell and my requirement is to auto generate it when open the template. See the following directory.

e.g: C:\Directory -->

          Template.xls

          Invoice_01.xls

          Invoice_02.xls

When I open the Template.xls it should show 03 in Invoice Number cell and when I save the file it should be saved with a new file name Invoice_03.xls.

Is it possible to do this? Please help.

Macros & VBA
Need Help

1 Reply

  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    Jun 18, 2018

    Hey Chamara-

     

    Hope your doing well.  Yes, this is possible with a little VBA Code:

     

    You will need a workbook open event that triggers when the workbook is opened as well as a beforesave event that triggers before save as the naming convention denotes.  If your Invoice number is in cell A1 for example then you would want to do something like this:

     

    This code needs to be put in the ThisWorkbook Code Module:

     

    Private Sub Workbook_Open()
    Range("A1").Value = Range("A1").Value + 1
End Sub

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
     Application.EnableEvents = False
     rtn = Application.Dialogs(xlDialogSaveAs).Show(arg1:="My FileName Invoice " & Range("A1").Value)
     Application.EnableEvents = True
     If Not rtn Then Cancel = True
    End Sub

     

Resources