Save a the same workbook but add a variable number at the end

[swade730]

HI, I want to save down a template as the same named file. each time I save it I want to save it with an incremental variable number\

example
"myworkbook ver 1"
"myworkbook ver 2"

This is what I'm trying to leverage and build on but I am stuck...

this just keeps adding .xlsx to the same file, not different versions

Sub Save()

Dim Folder
Dim File
Dim Month



    Folder = Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, "\New Files\"))

     File = Left(Activeworkbook.Name, Len(Activeworkbook.Name))



   'Thisworkbook.Sheets("Info").Range("A1") = "Sept"
    Month = ThisWorkbook.Sheets("Sheet1").Range("A1").Value
    
    
    Activeworkbook.SaveAs FileName:= _
        "" & Folder & "" & File & "-" & Month & ".xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    
    

End Sub

Please Help

[rkey]

Hi Swade,

I usually append this to my filename:

 Format(Now(), "yyyymmdd_hhmmss")

Does that suit your needs?
Regards,
Rick

[swade730]

Hi Swade,

I usually append this to my filename:

 Format(Now(), "yyyymmdd_hhmmss")

Where would I insert this?

[JOHN H. DAVIS]

Maybe:

Sub swade730()
Dim file As String, file2 As String
file = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5)
file2 = Left(Right(ActiveWorkbook.Name, 5), 1)
ActiveWorkbook.SaveAs file & file2 + 1 & ".xlsx"
End Sub

[swade730]

Maybe:

Sub swade730()
Dim file As String, file2 As String
file = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5)
file2 = Left(Right(ActiveWorkbook.Name, 5), 1)
ActiveWorkbook.SaveAs file & file2 + 1 & ".xlsx"
End Sub

The below Line confuses me it is making file2 value as "." in the Locals window

file2 = Left(Right(Activeworkbook.Name, 5), 1)

[JOHN H. DAVIS]

My mistake. Should be:

file2 = Left(Right(Activeworkbook.Name, 6), 1)

[swade730]

My mistake. Should be:

file2 = Left(Right(Activeworkbook.Name, 6), 1)

No worries, Marking this Thread as solved

[skywriter]

My code assumes you have a file named something like this.
C:\MyFolder\MyFileRev1.xls

Fpath returns the full path seen above.

My code initially looks for "Rev" in fpath

If you had a brand new file that didn't have Rev in the path you could check for that and name it Rev1 and bypass the rest of the code.

Then the newName line concatenates the 2 onto the original file name on the first loop. Dir checks if the file exists. With Dir if the file exists already it will return the file name, otherwise it returns "". So by checkingfor "" we confirm the file doesn't exist. revNumber will just keep changing until it finds the first rev number file that doesn't exist and once it does it saves the file and exits the loop.

Sub SaveNewVersion()
Dim fPath As String
Dim newName As String
Dim revPosition
Dim revNumber As Long

fPath = ThisWorkbook.FullName

revPosition = InStr(1, fPath, "Rev")

For revNumber = 2 To 100
    newName = Left(fPath, revPosition + 2) & revNumber & ".xls"
        If Dir(newName) = "" Then
            ThisWorkbook.SaveAs newName
            Exit Sub
        End If
Next revNumber

End Sub