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?