save file without overwriting existing file in VB Macro

Hi BillZab ,

I added some comments to the code to make it easy to find where to edit it.

Option Explicit
Dim HasWeekFile As Boolean 'Variable to check if the file name exists
Sub SavePDF()
    
    Dim MyDir As String
    Dim MyFileName As String
    Dim MyWeekNo As String
    HasWeekFile = False 'The standard value of this variable should be False
    
    MyWeekNo = "4" 'Fake value assigned here. You probably extract it dynamically in your code. Make sure to edit it when importing.
    MyFileName = "Week " + MyWeekNo & Format(Range("A2").Value, "-yyyy ") 'Build the file name with "Week " (constant), MyWeekNo (variable) and the year value from cell A2 from current Sheet

    MyDir = "C:\Users\" + strUser + "\OneDrive\Documents" 'Define the dir where the file will be saved (and where the existent files will be searched)
    ChDir MyDir
    Call GetFilesInFolder(MyDir, MyFileName) 'Call GetFilesInFolder method
    
    If HasWeekFile = True Then 'In case of HasWeekFile be assigned as True, file name will receive the "(1)". It is possible to edit it to create a (2), (3) and so on dynamically
        MyFileName = MyFileName & "(1)"
    End If
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
        MyFileName, Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
    
End Sub

Sub GetFilesInFolder(FolderPath As String, FileName As String)
    Dim oFSO As Object 'Variable to run the File System
    Dim oFolder As Object 'Variable to access the folder (MyDir, in this code)
    Dim oFile As Object 'Variable to run each file inside MyDir
    Dim i As Integer 'Index for iteration. You can remove it, as it won't be used in this code.
    Dim PdfToFind As String 'Name of the file with .pdf extension
    
    PdfToFind = Trim(FileName) & ".pdf" 'Remove the empty spaces in start/end of file name (MyFileName) and add extension .pdf
    
    Set oFSO = CreateObject("Scripting.FileSystemObject") 'Initialize File System
    
    Set oFolder = oFSO.GetFolder(FolderPath) 'Set MyDir as path to search the files
    
   For Each oFile In oFolder.Files 'Loop that iterate all files inside MyDir
    
        If oFile.Name = PdfToFind Then 'Check if the current file name is the same of MyFileName plus .pdf and, if yes, assign variable HasWeekFile as true
            HasWeekFile = True
        End If
    
        i = i + 1 'You can remove it, as it won't be used in this code.
    
    Next oFile 'Jump to the next file. 
End Sub

Just to confirm: your expected output is something similar to the image below, where your current sheet will be stored as "MyFileName (1)", right?

Let me know if you need any additional help.