Auto number and save

  • Thread starter Thread starter mark
  • Start date Start date
M

mark

Hi
is it possible to have an incremental numer in a cell ie invoice number also
to create macro to save this number as the file name. So every time i enter
data in the form it gives it a unique number and save it with that number

Mark
 
Mark
The following macro saves the workbook under the name of the contents of
cell A1 in the same path as the original file. Note that this message may
wrap the code over 2 lines. The macro consists of the Sub line, the End Sub
line, and only one line in between.
Sub SaveTest()
ActiveWorkbook.SaveAs ActiveWorkbook.Path & "\" & Range("A1").Value &
".xls"
End Sub

You asked also about incrementing the number in A1. I assume you want this
to happen automatically. The code in the macro is a simple one-liner:
Sheets("TheSheetName").Range("A1") = Sheets("TheSheetName").Range("A1") + 1
However, the macro that you put it in depends on when you want the number to
increment. At file opening? At the command to print? At file saving?
Below is the Before_Print macro:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Sheets("TheSheetName").Range("A1") = Sheets("TheSheetName").Range("A1")
+ 1
End Sub
Again, watch out for line wrapping.
The above Before_Print macro is a workbook event macro and. as such, must go
in the workbook module. You can access that module by right clicking on the
Excel icon to the left of the word "File" in the menu across the top of the
worksheet, selecting View Code, and pasting the macro into that module. You
can X-out of that to get back to the worksheet.
HTH Otto
 
Thank you that sorted the save problem re the incrementing number how do i
do this for on file open


Thanks

Mark
 
Private Sub Workbook_Open()
Sheets("TheSheetName").Range("A1") = _
Sheets("TheSheetName").Range("A1") + 1
End Sub

Mark
The underline character allows you to put the code on 2 lines for
readability. You can remove it and have it all on one line if you wish.
Put this macro in the same module I mentioned before. HTH Otto
 
Back
Top