I’m struggling with a random ‘File in Use’ Error when running my VBA routines. At a high level, I have a ‘Master’ Workbook that I am making a copy of and putting it in an Archive directory. There are instances where I’m running the routine multiple times a week, therefore, I’m overwriting (killing) the Archived version for that time period with the latest copy.

But there are times I get the standard ‘File in Use’ error. The strange thing is, it doesn’t say what user, it just says ‘another user’. I’m wondering if my VBA code is causing a hung excel pid or something? Would there be anything in this code that’s causing my issue?

Sub ClientPrep()

    Dim wbTarget As Workbook
    Dim strPathBin As String
    Dim strSSPath As String
    Dim strTab As String
      
    With Application
        .DisplayAlerts = False
        .AskToUpdateLinks = False
        .AlertBeforeOverwriting = False
        .ScreenUpdating = False
        .Calculation = xlManual
    End With
    
    dd = Format(Now, "dd")
    mm = Format(Now, "mm")
    m = Format(Now, "m")
    q = Format(Now, "q")
    yy = Year(Now)

    '''
    Sheets("Results").Activate
    WeekEndDate = Application.WorksheetFunction.Max(Columns("F"))
    WeekEndDate = Format(WeekEndDate, "mm-dd-yyyy")
    
    Sheets("Cover Page").Activate
    
    strPathBin = ""
    strSSPath = ActiveWorkbook.Path & "_Client_Copy" & WeekEndDate & ""

    '::-- Set batch variables --::'
    Shell ("cmd.exe /C SETx ClientCopyBin " & "_Client_Copy" & WeekEndDate & "")
    Shell ("cmd.exe /C SETx WeekEndDate " & WeekEndDate)

    '::-- Create Archive Directory --::'
    If Dir(strSSPath, vbDirectory) = "" Then
        Shell ("cmd /c mkdir """ & strSSPath & """")
    End If

    '::-- Copy Files to Archive Directory --::'
    Application.Wait (Now + TimeValue("00:00:02")) 'wait 2 seconds from now

    '::-- Get Client Name --::'
    On Error Resume Next
    strString = Replace(ThisWorkbook.Worksheets("Cover Page").Range("C2"), " ", "_")
    
    If Err.Number <> 0 Then
        Exit Sub
    End If
       
    strName = WeekEndDate & "_" & strString & "_Hours_Report.xlsm"
                        
    '::-- Remove Worbook if Exist --::'
    If Dir(strSSPath & strName) <> "" Then Kill strSSPath & strName

    '::-- Make Workbook Copy --::'
    ActiveWorkbook.SaveCopyAs strSSPath & strName
    
    '::-- Open Client Workbook --::'
    Set wbTarget = Workbooks.Open(strSSPath & strName)

    '::-- Hide Period Tabs if no data exists --::'
    Dim i As Integer
    For i = 1 To 12
    
        strTab = "P" & i
        
        Sheets(strTab).Cells.Copy
        Sheets(strTab).Cells.PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
        
        If i > m Then wbTarget.Worksheets(strTab).Visible = xlSheetHidden
        
        wbTarget.Worksheets(strTab).Range("B:B").EntireColumn.AutoFit
        Sheets(strTab).Activate
        ActiveSheet.Cells(1, 1).Select
    Next
             
    For i = 1 To 4
        
        strTab = "Q" & i
        
        Sheets(strTab).Cells.Copy
        Sheets(strTab).Cells.PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
                
        If i > q Then wbTarget.Worksheets(strTab).Visible = xlSheetHidden
        
        wbTarget.Worksheets(strTab).Range("B:B").EntireColumn.AutoFit
        Sheets(strTab).Activate
        ActiveSheet.Cells(1, 1).Select
        
    Next i
    
    Sheets("Overage_Tracker").Cells.Copy
    Sheets("Overage_Tracker").Cells.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    ActiveSheet.Cells(1, 1).Select
                 
    '::-- Clear Results Tab --::'
    wbTarget.Worksheets("Results").Cells.Clear

    
    '::-- Hide Sheets --::'
    On Error Resume Next
    wbTarget.Worksheets("Contract").Visible = xlSheetHidden
    wbTarget.Worksheets("Results").Visible = xlSheetHidden
    wbTarget.Worksheets("Overage_Tracker").Visible = xlSheetHidden
    wbTarget.Worksheets("Instructions").Visible = xlSheetHidden
    wbTarget.Worksheets("Task").Visible = xlSheetHidden
    wbTarget.Worksheets("Rate_Card").Visible = xlSheetHidden
    wbTarget.Worksheets("Non-Standard_Tracker").Visible = xlSheetHidden
    On Error GoTo 0
    
    wbTarget.Worksheets("Cover Page").Activate
    wbTarget.Save: wbTarget.Close
    ActiveWorkbook.Worksheets("Cover Page").Activate
    
    
End Sub