.Add After:=Sheets(Sheets.Count) not working anymore

I have this code that I worte a while ago, now all of a sudden I have errors popping up every where even something as simple as a .Add Sheet. I get a 'Method 'Add' of object 'Sheet' failed' error on the highlighted line below. Even though the sheet is add to the workbook.

Option Explicit
Option Base 1
Public CustName_Array() As String
Dim ERws As Worksheet, newSheet As Worksheet
Dim erlRow As Long, lRow As Long
Dim i As Long, rCnt As Long, CustName_cnt As Long
Dim CustNameCol As String

Sub addSheet()
    Set ERws = Worksheets("EE Report PHL")
    Sheets.Add After:=Sheets(Sheets.Count)
    Set newSheet = ActiveSheet
    CustNameCol = findcolumn("Customer Name OM", ERws, 1, True)
    With ERws
        erlRow = .Cells(Rows.Count, "A").End(xlUp).Row
        .Range(CustNameCol & "1:" & CustNameCol & erlRow).Copy
        With newSheet
            .Cells(1, 1).PasteSpecial xlPasteAll
             Application.CutCopyMode = False
            lRow = .Cells(Rows.Count, "A").End(xlUp).Row
            .Range("$A$1:$A$" & lRow).RemoveDuplicates Columns:=1, Header:=xlYes
            'Sort ActiveSheet
            With .Range("A1").CurrentRegion
                .Sort _
                Key1:=Columns(1), Order1:=xlAscending, _
            End With
            lRow = .Cells(Rows.Count, "A").End(xlUp).Row
        End With
        .Cells.Replace "#EMPTY", "", xlWhole
    End With
    'Create a 1-dimensional array & fill it with the values
    CustName_cnt = lRow - 1
    ReDim CustName_Array(1 To CustName_cnt)
    i = 1
    For i = 1 To CustName_cnt
        CustName_Array(i) = newSheet.Range("A" & i + 1)
    Call removeNonPA_CustomerNames
    Application.DisplayAlerts = False
    Application.DisplayAlerts = True
End Sub

 Also when I get to this sub:

Sub saveFile()
Dim tempFilePath As String, tempFileName As String
Dim fileExt As String, finalFileName As String
Dim fName As String, LName As String, colLTR As String
Dim fileFormat As Long

    Application.DisplayAlerts = False
    Application.CalculateBeforeSave = False

    tempFileName = "PHL-EE Report"
    'Set the filepath
    If UserForm1.txt_SavetoFolder.Value = "" Then
        tempFilePath = Application.DefaultFilePath & "\"
        tempFilePath = UserForm1.txt_SavetoFolder.Value & "\"
    End If
    finalFileName = tempFileName & "_" & Format(Now, "mmddyy hhmm_AMPM")
    fileExt = ".xlsx"
    fileFormat = 51    ''''
    ActiveWorkbook.SaveAs tempFilePath & finalFileName & fileExt, fileFormat:=fileFormat
    Application.DisplayAlerts = True
End Sub

I get a 'Document not Saved' error. I have been running this code at least once a week for the past 3 to 4 month. Now , all of a sudden, it's not working anymore. Any reason why?


Question Info

Last updated September 5, 2019 Views 992 Applies to:

    Sheets.Add after:=Worksheets(Worksheets.Count), Type:=xlWorksheet
    Worksheets.Add after:=Worksheets(Worksheets.Count)

If this response answers your question then please mark as Answer.
GMT+1 - Europe

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.