.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
        .Select
        erlRow = .Cells(Rows.Count, "A").End(xlUp).Row
        .Range(CustNameCol & "1:" & CustNameCol & erlRow).Copy
        With newSheet
            .Select
            .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
            Columns("A:A").EntireColumn.AutoFit
            'Sort ActiveSheet
            With .Range("A1").CurrentRegion
                .Sort _
                Key1:=Columns(1), Order1:=xlAscending, _
                Header:=xlYes
            End With
            lRow = .Cells(Rows.Count, "A").End(xlUp).Row
        End With
        .Select
        .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)
    Next
    Call removeNonPA_CustomerNames
    Application.DisplayAlerts = False
    Sheets(Sheets.Count).Delete
    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 & "\"
    Else
        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:
Answer
Answer
Hi

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

HTH
Regards
JY
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.