Save as PDF

[BrendanDixon]

Hi All,

I have a sheet where I click on a button and it will save the sheet as a PDF file with a predetermined name. The Problem I have is that I sometimes might do multiple sheets on the same day so when I click the button it will overwrite the first form. I am wonderinf is someone could tell me how to edit my code to append the filename with a 1 or 2 or 3 etc. This is my code below.

ActiveWindow.SelectedSheets.PrintOut Copies:=(Sheets("Input").Range("C16").Value), Collate:=True
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=(Sheets("Input").Range("C17").Value & "\" & Sheets("Input").Range("C8").Value & " " & Sheets("Input").Range("D8").Value & " " & Sheets("Input").Range("E8").Value & " " & Sheets("Input").Range("C4").Value & ".pdf"), Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

 

[Joe4]

Here is a little snippet of code you should be able to incorporate into your macro. It checks to see if the file name already exists, and if it does, it adds a counter to the end (1,2,3,...etc until it finds the first unused one).

    Dim myFileName As String
    Dim myFileNamePrefix As String
    Dim i As Long
 
'   Build file name here
    myFileName = "G:\C\Temp\Book1.pdf"
    myFileNamePrefix = Left(myFileName, Len(myFileName) - 4)
    
'   Loop until you find an unused filename
    i = 0
    Do
        If Not (Dir(myFileName) = vbNullString) Then
            i = i + 1
            myFileName = myFileNamePrefix & i & ".pdf"
        Else
            Exit Do
        End If
    Loop
    
    MsgBox myFileName

 

[BrendanDixon]

Thanks,

Will give it a try and let you know.