Revision Numbering File Name?

[doleawg]

Hi,

New here and got what I thought was a simple problem to solve that is driving me mad. I have a file with numerical values that often change. I want to set up my file so that every time I save it changes to a new number so that over time I can reflect back.

I think I would prefer to keep the revision to a simple number rather than date. For example if the file name was 'test', then I would like it to go 'Test 1, then test 2', with the highest number being the newest. If date stamp is the only option I would live with it.

Hope this makes sense and any help appreciated. Going mad.....

[JOHN H. DAVIS]

One suggestion. Create a new sheet (sample is Sheet 3). Down Column A list consecutive numbers. Then maybe something like this.

Please Login or Register  to view this content.

[doleawg]

Did a quick test and it seems to work. Sadly the spreadsheet I was to use it on is on my work PC and that is now turned off and travelling early tomorrow so unlikely to get a chance to test till late tomorrow. Will let you know.

Thanks

[doleawg]

Hi, I tried you code and nearly there. I made 2 changes. Sheet 1 is where the numbers are and I changed the file extension to .xlsx as I am using 2010. However it will save 'new file name-1' but when I try open that doc it says in Excel that it won't open the file as 'file format or file extension is not valid'? Do I have to keep in .xls file format?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="FILE LOCATION AND NAME" & " " & Sheets("Sheet1").Range("A1").Value & ".xlsx", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Sheets("Sheet1").Range("A1").Delete xlUp
Application.DisplayAlerts = True
End Sub

[JOHN H. DAVIS]

I have 2003 and I'm afraid I can help you with that part. I've never received that message. I think it may have to do with this portion of the code though -- FileFormat:=xlNormal, Password:="", WriteResPassword:="", _

Try changing it back to xls. I'm sure some Guru on the board will have a better answer them I can provide.

http://msdn.microsoft.com/en-us/libr...ffice.12).aspx