Save Functions‎ > ‎

SaveAs Increment

PROBLEM:
"Each time I select SaveAs I would like the file to save automatically with a specific filename in a specific spot, but the filename increments each time. The filename will include today's date and an increment number, like ROTN dd-mmm-yy (1).xls  The next SaveAs would change to (2) automatically."

SPECIFICATIONS:
  1. Only happens on SaveAs, regular SAVE still operates
  2. Path and base file name stay the same, just date and (number) increment automatically.
  3. Code goes into the ThisWorkbook module

CODE

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Jerry Beaucaire (8/4/2010)
'Incremental SaveAs routine
Dim SvName  As String
Dim SvNext  As String
Dim SvStr   As String
Dim Incr    As Long

SvStr = "C:\2010\ROTN " & Format(Date, "dd-mmm-yy") & "(^)"
Incr = 1
Application.EnableEvents = False

    If SaveAsUI = True Then
        Do
            SvNext = Replace(SvStr, "^", Incr)
            SvName = Dir(SvNext & ".*")
            If Len(SvName) = 0 Then
                ThisWorkbook.SaveAs SvNext, xlNormal
                Exit Do
            Else
                Incr = Incr + 1
            End If
        Loop
    Else
        Me.Save
    End If

Cancel = True
Application.EnableEvents = True
End Sub




Nothing says "thanks" like a steak dinner!
PayPal - The safer, easier way to pay online!
Comments