Automatically rename the file if it exists

nirvanaraaz · Jan 15th 2023

I have the specified the location and file name on the sheet1 but when i save the file everytime as pdf if the Range.C42 contains the same value the file automatically get replaced. How to create new file if the C42 value is same but without replacing the previous file.

Code

Sub SelectSheetsAndSaveAsPDF()
Dim strPathLocation As String
Dim Filename As String
Dim sheetArray As Variant


strPathLocation = Sheets("sheet1").Range("C41").Value
Filename = Sheets("sheet1").Range("C42").Value
sheetArray = Array("sheet2", "sheet3")


Sheets(sheetArray).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=strPathLocation & Filename


End Sub

Display More

rollis13 · Jan 15th 2023

Add these lines of code to your macro, could be a simple solution. It will add a random number at the end of Filename:

Code

'...
    Sheets(sheetArray).Select
    '---------- added ---------
check:
    If Dir(strPathLocation & Filename & ".pdf") <> "" Then
        Filename = Filename & "_" & Int((10 * Rnd))
        GoTo check
    End If
    '---------------------------
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=strPathLocation & Filename
End Sub

Display More

nirvanaraaz · Jan 16th 2023

thank you. it worked perfectly

rollis13 · Jan 16th 2023

Thanks for the positive feedback, glad having been of some help.

Automatically rename the file if it exists

Participate now!

Share

Similar Threads

Automatically save file to specific folders base on cell name????

Use Submit button to convert Worksheet(s) to PDF and email via Outlook

Update VBA Code to Include Parameter for Delimiter, File Location and Overwrite Sheets when Re-Run

Excel to PowerPoint automatic update Code - Does not work after reopening the file

Open another workbook on current workbook beforeclose event

If data changes, how to show only new changes?