Export excel file as PDF into a targeted pc folder based on cell value

[Ramadan2512]

I have a code in excel sheet to save the currant page as pdf into pc folder based on cell value + current month text and it's working perfectly with just one problem - when I have an old file already saved in the folder and I need to save a new one with the same month name, the new file override the old one and I lose it - for example a file saved as (AV 24A - P3) it will be saved in my folder as (AV 24A - P3 October) but if i have a new file with the same name (AV 24A - P3) and i need to save it now in the same month I will lose the old one because it will get the same name like (AV 24A - P3 October)
So, I need please to edit my code that if the file name is already found in the folder to add for example: AV 24A - P3 October (1), (2) etc.. to the new file name and not to override the old existing file - here is my code

Sub ExportToPDF()
   
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
   
    Dim sName As String
   
    Select Case ws.Range("M7").Value
        Case 1: sName = "NH1 (District One)\Guards"
        Case 2: sName = "NH2 (Carnell Park)\Guards"
        Case 3: sName = "NH3 (Ivory Hills)\Guards"
        Case 4: sName = "NH4 (Westridge)\Guards"
        Case 5: sName = "NH5 (Gold Cliff)\Guards"
        Case 6: sName = "NH6 (Kingsrange)\Guards"
        Case 7: sName = "NH7 (Amberville)\Guards"
        Case 8: sName = "NH8 (Kingsrange PH2)\Guards"
        Case Else:
            MsgBox "Enter a number from 1 to 8 in cell 'M7'!", vbExclamation
            Exit Sub
    End Select
   
    Dim FilePath As String:
    FilePath = "D:\NEWGIZA\" & sName & "\" & ws.Range("M5").Value & " " _
        & Application.Text(Date, "[$-401]mmmm") & ".pdf"
 
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FilePath, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=True

End Sub

 

[DanteAmor]

Try this:

Sub ExportToPDF()
  Dim ws As Worksheet
  Dim sName As String, FilePath As String, NewName As String, sExt As String
  Dim n As Long
  
  Set ws = ActiveSheet ' improve!
  Select Case ws.Range("M7").Value
    Case 1: sName = "NH1 (District One)\Guards"
    Case 2: sName = "NH2 (Carnell Park)\Guards"
    Case 3: sName = "NH3 (Ivory Hills)\Guards"
    Case 4: sName = "NH4 (Westridge)\Guards"
    Case 5: sName = "NH5 (Gold Cliff)\Guards"
    Case 6: sName = "NH6 (Kingsrange)\Guards"
    Case 7: sName = "NH7 (Amberville)\Guards"
    Case 8: sName = "NH8 (Kingsrange PH2)\Guards"
    Case Else:
      MsgBox "Enter a number from 1 to 8 in cell 'M7'!", vbExclamation
      Exit Sub
  End Select
  
  FilePath = "D:\NEWGIZA\" & sName & "\" & ws.Range("M5").Value & " " _
    & Application.Text(Date, "[$-401]mmmm")
  sExt = ".pdf"
  NewName = FilePath & sExt
  
  Do While True
    If Dir(NewName) = "" Then Exit Do
    n = n + 1
    NewName = FilePath & " (" & n & ")" & sExt
  Loop
  
  ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=NewName, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

 

[Ramadan2512]

Try this:

Sub ExportToPDF()
  Dim ws As Worksheet
  Dim sName As String, FilePath As String, NewName As String, sExt As String
  Dim n As Long
 
  Set ws = ActiveSheet ' improve!
  Select Case ws.Range("M7").Value
    Case 1: sName = "NH1 (District One)\Guards"
    Case 2: sName = "NH2 (Carnell Park)\Guards"
    Case 3: sName = "NH3 (Ivory Hills)\Guards"
    Case 4: sName = "NH4 (Westridge)\Guards"
    Case 5: sName = "NH5 (Gold Cliff)\Guards"
    Case 6: sName = "NH6 (Kingsrange)\Guards"
    Case 7: sName = "NH7 (Amberville)\Guards"
    Case 8: sName = "NH8 (Kingsrange PH2)\Guards"
    Case Else:
      MsgBox "Enter a number from 1 to 8 in cell 'M7'!", vbExclamation
      Exit Sub
  End Select
 
  FilePath = "D:\NEWGIZA\" & sName & "\" & ws.Range("M5").Value & " " _
    & Application.Text(Date, "[$-401]mmmm")
  sExt = ".pdf"
  NewName = FilePath & sExt
 
  Do While True
    If Dir(NewName) = "" Then Exit Do
    n = n + 1
    NewName = FilePath & " (" & n & ")" & sExt
  Loop
 
  ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=NewName, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

Woww that's great, you are much professional my friend - it works perfectly - I do appreciate your help - thanks a million