ExcelBanter - 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