Runtime Error 1004 + Runtime 5

Full disclosure, I have posted this code to this website twice before, and both issues were resolved.
I am receiving a Runtime error 1004 and a runtime error 5 when I run my code. I am not sure which lines is throwing the error. Excel presents the warning but does not take me to the line causing the error.

I am looping through a range that was generated by a pivot table, so I know the range items are valid. I am filtering said pivot tables by the range list. From there I am saving the file to a folder that exists.
The odd part is, the code will execute (never completely) with no issue and other times (at various places in the list) it will throw the error.

I have included my code below a sample was too big to attach (no matter what I did to shrink the file size). Any help would be greatly appreciated,

Option Explicit

Sub ListMissingItems()

'--------------------------------------------------------------------------------------------------
'
' AUTHOR: JOSEPH RINALDI - BUDGET & FORECASTING DEPARTMENT
' PURPOSE: THE PURPOSE OF THIS SCRIPT IS TO TAKE A MASTER FILE AND BREAK IT INTO INDIVIDUAL PIECES
'
' THE FILE ASSOCIATED WITH THIS SCRIPT IS:
'       File: 2020 Forecast 6x6 Template.xlsm
'       Creator of file: John Lariviere
'       Path: Y:\Budget process information\2020 Financial Activities\2020 Plan & Forecast\2020 Forecast\
'
'--------------------------------------------------------------------------------------------------




'----------------------------------------------------------------------
'
' WORK WITH THE MASTER EXCEL FILE: 2020 Forecast 6x6 Template.xlsm
' DEFINE RANGES AND OBJECTS (PIVOT TABLES)
'
'----------------------------------------------------------------------




''Confirm that the user wants to complete action
'    If MsgBox("This action will Create a file for Each Department, " & vbNewLine & _
'                    "Do you want to continue? ", vbCritical + vbYesNo, "WARNING") = vbYes Then

    Dim pt As PivotTable
    Dim pt_details As PivotTable
    Dim pf As PivotField
    Dim pf_details As PivotField
    Dim pi As PivotItem
    Dim rngList As Range
    Dim sFileLocation As String
    Dim StartTime As Double
    Dim MinutesElapsed As String
    
'PURPOSE: Determine how many minutes it took for code to completely run
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault
'Remember time when macro starts
  StartTime = Timer
  
    'Error handling
        'On Error GoTo Error_Handler
    
    'Turn on Optimize_VBA
        Optimize_VBA True
    
    'Set where you want the reports to be saved
    sFileLocation = "Y:\Budget process information\2020 Financial Activities\2020 Plan & Forecast\Exported Templates\"
    
    'Test Location
    'sFileLocation = "Y:\Budget process information\2020 Financial Activities\2020 Plan & Forecast\Exported Templates\Exported Templates _2\"
    
    'Range of departments to iterate through on each of the pivot tables
        Set rngList = Sheet6.Range("F5:F43")
    
    'The 2 pivot tables that we are going to iterate over
        Set pt = Sheet2.PivotTables("PivotTable1")
        Set pt_details = Sheet5.PivotTables("PivotTable1")
    
    
    'The criteria field within the pivit table
        Set pf = pt.PivotFields("EXHIBIT_2_DEPT1")
        Set pf_details = pt_details.PivotFields("EXHIBIT_2_DEPT1")
    
    
'----------------------------------------------------------------------
'
'BEGIN FOR EACH LOOP
'
'----------------------------------------------------------------------
    
    For Each pi In pf.PivotItems
    
    ' Confirm we are stepping through the range (department) list correctly
        Debug.Print pi.Caption
    
    'Filter Pivot to the "pi.Caption"
    
    'Filter Pivot on "Pivot" tab by the department stored in "pi.Caption"
        pf.CurrentPage = pi.Caption
        'pf.CurrentPage = "8000 - SK Office"
    'Filter Pivot on "Details" tab by the department stored in "pi.Caption"
        pf_details.CurrentPage = pi.Caption
    
    'filter table on "Template" to "Leave"
        Worksheets("Template").Range("$A$10").AutoFilter Field:=1, Criteria1:="Leave"



'###########################################################################################################################
'FROM THIS POINT ON, WE ARE WORKING WITH THE INDIVIDUAL DEPARTMENTS WORKBOOK

   'Begining of new file check point
    Debug.Print "Starting to work with the: " & pi.Caption & " File"

    'Create the new workbook and Save the workbook to file location
    Dim wb As Workbook
    Sheets(Array("Contents", "Template", "Details")).Copy
    Set wb = ActiveWorkbook

    'This will suppress the alert dialog boxes
    Application.DisplayAlerts = False
    wb.SaveAs sFileLocation & pi.Caption & " 2020_Forecast" & ".xlsx"
    Application.DisplayAlerts = True

'    Dim sFileName As String
'    sFileName = Pi.Caption & " 2020_Forecast" & ".xlsx"

    'For each cell in range G, H, J, M O.  If not a SUM formula, copy and paste values
    Dim Rg As Range, c As Range
    With wb.Worksheets("Template")

        'Starting clean
         Debug.Print "Starting clean up on: " & pi.Caption & " File"
        
        'With sFileName.Sheets("Template").UsedRange
        Set Rg = Intersect(Union(.Columns("G:H"), .Columns("J:J"), .Columns("N:N"), .Columns("P:P")), .UsedRange)

        For Each c In Rg
            If Not c.Formula Like "=SUM(*" Then c.Value = c.Value
        Next

        'Delete hidden rows
        Dim ws As Worksheet
        Dim iCntr As Long
        Dim lastRow

        lastRow = 700
        For iCntr = lastRow To 1 Step -1
            If .Rows(iCntr).Hidden = True Then .Rows(iCntr).EntireRow.Delete
        Next

'I BELIEVE THE ERROR IS IN HERE 

       'Worksheets("Template").ShowAllData
        .ShowAllData
        .Columns("A:B").EntireColumn.Delete
        .Columns("E:F").Hidden = True
        .Range("A3").Copy
        .Range("A3").PasteSpecial Paste:=xlPasteValues
    End With

    'Go to the details sheet and value out the pivot table.  The do some clean up.
    With wb.Worksheets("Details")
        .Range("A8").CurrentRegion.Copy .Range("I8") 'value out pivot
        .Columns("A:H").EntireColumn.Delete
        .Columns("A:G").EntireColumn.AutoFit
        .Range("A8").Select

    End With
    
    'Bring the focus back on the Template worksheet
    wb.Worksheets("Contents").Activate

    'Save and close workbook
    wb.Close SaveChanges:=True
    
    'EXITING THE INDIVIDUAL DEPARTMENTS WORKBOOK

'###########################################################################################################################
   
'Saved and moving to the next department
    Debug.Print "The Following workbook has been created and Saved: " & pi.Caption

    Next pi
    
'Turn on Optimize_VBA
    Optimize_VBA False

'Determine how many seconds code took to run
  MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
  
'Notify user in seconds
  MsgBox "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation
  
'Confirmation
    'MsgBox "Success!  That's some good work ", vbInformation, "GREAT JOB"
    
    
    
''Exit if user clicks no
'Else
'        MsgBox "D'OH!      ", vbInformation, "CHECK YA LATER!"
'End If
'Exit Sub
'

    
'This is a work in process, not sure what errors I will need to handle yet
Error_Handler_Exit:
            Exit Sub

Error_Handler:
            Select Case Err.Number
'                Case 1004
'                    Err.Clear
'                    Resume Next
                Case Else
                    MsgBox "Error No. " & Err.Number & vbCrLf & "Description: " & Err.Description, vbExclamation, "Database Error"
                    Err.Clear
                    Resume Error_Handler_Exit
            End Select
    
    
End Sub



Sub Optimize_VBA(isOn As Boolean)

    Application.Calculation = xlAutomatic
    Application.EnableEvents = Not (isOn)
    Application.ScreenUpdating = Not (isOn)
    
End Sub

I had to strip all the formatting away and delete a few lines, but this is a good representation of what the file is.

Originally Posted by jrean042

... this is a good representation of what the file is ...

No ... because there is no:

1. "Sheet6"

Set rngList = Sheet6.Range("F5:F43")

2. "Sheet2"

Set pt = Sheet2.PivotTables("PivotTable1")

in your file.

I'm not sure I can reduce that file down enough to attach. Let me do my best.
I honestly don't think that part of the code is the issue though.

I think its just too big, and there are links to an Access Database, not sure how I would upload a sample file.
Any guidance you can give without seeing the original file would help at this point.

Thank you for your help