saving in sequential numbering i.e 1,2 3 etc or by date/time

stevehorton09 · Jul 22nd 2004

Hi there,

Currently i have a vb statement that will copy a sheet and then save it to a certain location.

what im interested to do is to set it up so when i click the button the first is called 1, then 2 etc.

Also is it possible to save them as the current date and time??

my current code is below.

Sub savings()
'

Application.DisplayAlerts = False
ActiveSheet.copy
ActiveWorkbook.SaveAs Filename:="C:\SCH\1.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close
'

End Sub

Jong · Jul 22nd 2004

Okay, first, in order to generate an incremented number, you need to find out what number you are currently using. To do this, I suggest using the "ThisWorkbook" object. In the "ThisWorkbook" object, paste the following code (or something like this):

Code

Public Number as Integer


Private Sub Workbook_Open()
    
      Dim CurrentNumber as Integer
      Dim TempStr as String


       'Get current number out of workbook name
       'The following code fragment is crude, and untested (and is missing error control)... You may want to revise this when you use it
      TempStr = Left(ThisWorkBook.Name, Len(ThisWorkBook.Name) - 5)
      TempStr = Left(TempStr, 1)
      CurrentNumber = CInt(TempStr)


      'Increment the current number
      CurrentNumber = CurrentNumber + 1


      'Save it in Public variable
       Number = CurrentNumber


end sub

Display More

Okay, that takes care of the number. Now to save your new format you can paste this in any module:

Code

Sub savings() 


'Get current date, and format it
Dim TempDate as string
TempDate = Date
TempDate = Replace(TempDate, "/", "_")


'Save
Application.DisplayAlerts = False 
ActiveSheet.copy 
ActiveWorkbook.SaveAs Filename:="C:\SCH\" & TempDate & " " & ThisWorkbook.Number & ".xls", FileFormat:=xlNormal, _ 
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ 
CreateBackup:=False 
ActiveWorkbook.Close 


End Sub

Display More

stevehorton09 · Jul 22nd 2004

thats great thankyou,

do you know if it is possible to say have a series of numbers in a cell(a1)and then when you push save, it would then save it as the values in a1?

another question... is it possible to save something as the time and date of the save?

thanks for your help

saving in sequential numbering i.e 1,2 3 etc or by date/time

Participate now!

Share