| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 60507 | 2005-08-04 01:55:00 | Autonumber in Excel | indus (8658) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 378076 | 2005-08-04 01:55:00 | I am running Excel 2003 and wish to set up an invoice template. I would like to set up the invoice number as an autonumber which should change to the next every time an invoice is completed and saved to a specified folder as a new file with the invoice number as part of the file name. This would ensure the it does not overwrite the one saved earlier and all the invoices saved are available. Is it possible to set this up in excel using vb? Any help appreciated |
indus (8658) | ||
| 378077 | 2005-08-04 05:47:00 | Grab a coffee and have a gander thru this lot. Autonumber (www.google.com) hth |
johnboy (217) | ||
| 378078 | 2007-07-11 23:05:00 | I thought I'd post this even though the thread is old, maybe someone is still looking for a solution to this problem, I know I was, until I took the time to figure it out. Open a new workbook and enter a starting value in "A1". Copy text below into your VB editor inside Excel and save the file and close it. When you open the file again it will create a new file name based on the starting value you entered, save it, then increment the starting value in your original file by 1. Each time you open the original you'll get a new file name with the next number in series. Enjoy... Sub Auto_Open() ' ' Macro recorded 7/11/2007 Larry Doss ' Dim CSName As String Dim OldPath As String Dim OldFName As String 'This is the cell containing the name for the new book. CSName = Worksheets("Sheet1").Range("A1").Value 'This is the code to get path & Name data, not used. 'OldPath = ActiveWorkbook.Path 'MsgBox OldPath 'OldFName = ActiveWorkbook.Name 'MsgBox OldFName ActiveWorkbook.Sheets.Select 'Make a copy of the original workbook. Sheets.Copy ActiveSheet.Activate 'Save the new workbook. ActiveWorkbook.SaveAs (CSName) 'Close the new saved workbook. ActiveWorkbook.Close 'Close the original workbook Range("A2").Select ActiveCell.FormulaR1C1 = "=R[-1]C+1" Range("A2").Select Selection.Copy Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("A2").Select Selection.ClearContents ActiveWorkbook.Close (True) End Sub |
ldossjr (8659) | ||
| 1 | |||||