I am running into an issue with the following code where the templatepath is found and displayed on all computers running the macro, and copy pasting the path in the file explorer opens it, so all PCs have access to the file. However, using the code, I get the same message telling me that the path is empty when I ensure the template path is valid before copying. It only works on my computer.

Sub CopySheetFromTemplate()
    Dim wbTemplate As Workbook
    Dim lastWorkbook As Workbook
    Dim lastSheet As Worksheet
    Dim wsTarget As Worksheet
    Dim templatePath1 As String
    Dim templatePath2 As String
    Dim copyPath As String
    Dim templatePath As String
    Dim wsSource As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    ' Define the possible paths to the template file
    templatePath1 = "C:Program Files (x86)CasewareDataMateriality TemplateMateriality Analysis.xlsm"
    templatePath2 = "C:Program Files (x86)CasewareDataMateriality Template (Sync)Materiality Analysis.xlsm"
    copyPath = "C:Program Files (x86)CasewareDataMateriality TemplateMateriality Analysis_Copy.xlsm"

    ' Determine the correct template path (works on all computers)
    If Dir(templatePath1) <> "" Then
        templatePath = templatePath1
        MsgBox "Path 1 found.", vbInformation
    Else
        templatePath = templatePath2
        MsgBox "Path 2 found.", vbInformation
    End If

    ' Display the template path being used (works on all computers)
    MsgBox "Template path being used: " & templatePath, vbInformation
    
    ' Check if the copy already exists, if yes, delete it then make a copy
    If Dir(copyPath) <> "" Then
        On Error Resume Next
        Kill copyPath
        If Err.Number <> 0 Then
            MsgBox "Error deleting existing copy: " & Err.Description, vbCritical
            On Error GoTo 0
            Exit Sub
        End If
        On Error GoTo 0
    End If
    
    ' Ensure the template path is valid before copying (only works on my device)
    If Dir(templatePath) = "" Then
        MsgBox "The template file was not found at the specified path: " & templatePath, vbCritical
        Exit Sub
    End If

    ' Copy the template file using FileCopy
    On Error GoTo CopyFileError
    FileCopy templatePath, copyPath
    On Error GoTo 0

    ' Get the previously active workbook
    Set lastWorkbook = Application.ActiveWorkbook

    ' Replace specified substrings in sheet names
    For Each lastSheet In lastWorkbook.Sheets
        lastSheet.Name = Replace(lastSheet.Name, "General Ledger", "Sheet1")
        lastSheet.Name = Replace(lastSheet.Name, "QBV2", "QB")
        lastSheet.Name = Replace(lastSheet.Name, "GL", "QB")
    Next lastSheet
    
    ' Check if the required sheets exist in the workbook
    If Not SheetExists("QB_PVT_Sheet1", lastWorkbook) Or Not SheetExists("QB_Sheet1", lastWorkbook) Then
        MsgBox "Required sheets (QB_PVT_Sheet1 and/or QB_Sheet1) are missing in the workbook. Run the GL Cleaner first.", vbCritical
        Exit Sub
    End If

    ' Open the copy of the template file
    Set wbTemplate = Workbooks.Open(copyPath)

    ' Copy all sheets from the last active workbook to the template workbook
    For Each wsSource In lastWorkbook.Sheets
        wsSource.Copy After:=wbTemplate.Sheets(wbTemplate.Sheets.Count)
        ' Hide the copied sheet
        wbTemplate.Sheets(wbTemplate.Sheets.Count).Visible = False
    Next wsSource
      
    ' Notify the user
    MsgBox "Welcome to the Materiality Analysis Tool. Start by setting the Planning Materiality.", vbInformation
    
    ' Make the first sheet visible and activate it
    With wbTemplate.Sheets(1)
        .Visible = True
        .Activate
    End With
    
    Exit Sub

CopyFileError:
    MsgBox "An error occurred while copying the template file: " & Err.Description, vbCritical
End Sub

' Function to check if a sheet exists in the workbook
Function SheetExists(sheetName As String, wb As Workbook) As Boolean
    On Error Resume Next
    SheetExists = Not wb.Sheets(sheetName) Is Nothing
    On Error GoTo 0
End Function

Any help is appreciated…

I tried to change the file selection from FSO to Dir, didn’t work.
I added message boxes to see if the path is selected and it is.
I added a message Box at the copying step to alert the user that the path exists before copying, but it always returns “The template file was not found at the specified path:” and no path selected.

The code only works on my computer, although all computers have access to the file.