VBA Save file as

[Kra]

Hi all!

Is there a possibility to automate saving documents?

I always save in specific folder, so path can be defined in macro (desktop can be used as example) and I need to change file name before saving - I only add some text at the beginning (original name stays the same).

So lets say I have an worksheet called TEST. Marco needs to show box where user can add text which will be displayed at the beginning of worksheet name. So If user fills in "My file", worksheet is saved as "My file TEST" in specific folder.

 

[Kra]

I was able to crack it myself - here is code.

Sub ChangeFilename()


    Dim NewName As String
        NewName = InputBox("Insert name", "Change file's name")
        
    Dim CurrentName As String
        CurrentName = ActiveWorkbook.Name
    
    Dim WS As Workbook
    Set WS = ActiveWorkbook
    
    ActiveWorkbook.Windows(1).Caption = NewName & " " & CurrentName
    
    WS.SaveAs "C:\Public\KRA\" & NewName & " " & CurrentName
    
    End Sub

 

[smozgur]

Thanks for sharing the solution!

Just one note, SaveAs will already change the workbook's window caption as soon as the file is saved, so there is no need for an extra step to change the caption property.

 

[Kra]

Thanks for sharing the solution!

Just one note, SaveAs will already change the workbook's window caption as soon as the file is saved, so there is no need for an extra step to change the caption property.

Thanks, removed this line, learned something new!

 

[smozgur]

Thanks, removed this line, learned something new!