Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
increase number automatically
I want to automatically increase the number in a certain cell each time the
document is saved. I also would like the number in the document name to increase each time the document is saved (the new number will be the same for both the number in the cell and the document name). The document name is currently 0000Estimations (the 0s are where the new number will be). Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
increase number automatically
This can be achieved with a simple formula and macro Step 1 Save your file as 0001Estimations.xls In Cell A2 enter formula =CELL("filename",A1) This will return the full path, filename and worksheet name. In Cell A3 enter formula =FIND("000",A2) This will identify the position in the file name and path where your file number begins In Cell A1, use the value returned in cell A3 in place of nn in the following formula =MID(CELL("filename",A2),-nn-,4) You can now clear the contents of Cell A2 and A3. Step 2 Open the Visual Basic Editor Right Click on Modules in the top left hand window and Insert Module Copy and Paste the following code: Sub SaveAndRename() FileNum = ThisWorkbook.Sheets("Sheet1").[A1].Value + 1 FileNumStr = Format(FileNum, "0000") Newfilename = FileNumStr & "Estimations.xls" ActiveWorkbook.SaveAs Filename:=Newfilename, _ FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Calculate End Sub You can either create a button to run this macro instead of using File Save, or attach the macro to the Ctrl-S shortcut. starlw116 Wrote: I want to automatically increase the number in a certain cell each time the document is saved. I also would like the number in the document name to increase each time the document is saved (the new number will be the same for both the number in the cell and the document name). The document name is currently 0000Estimations (the 0s are where the new number will be). Thanks! -- CaptainQuattro ------------------------------------------------------------------------ CaptainQuattro's Profile: http://www.excelforum.com/member.php...o&userid=32763 View this thread: http://www.excelforum.com/showthread...hreadid=549750 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
increase number with 1% | Excel Worksheet Functions | |||
How to automatically number a new document(Invoice) when opening | Excel Discussion (Misc queries) | |||
How to automatically number a new document(Invoice) when opening | Excel Discussion (Misc queries) | |||
How to make Cells automatically become 'highlighted' when number . | Excel Worksheet Functions | |||
How to make Cells automatically become 'highlighted' when number . | Excel Worksheet Functions |