Automation Error?

  • Thread starter Thread starter Sean Stuber
  • Start date Start date
S

Sean Stuber

How can i make variable A = the sheet name being searched. I tried the
code below and got an automation error. Using Activesheet.Name did not
work either.
Thanks in advance.
---

Dim sh As Worksheet
Application.DisplayAlerts = False
For Each sh In Worksheets
If sh.Range("G19").Value = 2 Then
If Worksheets.Count > 1 Then
sh.Delete

'\ Record deleted sheets ***
Row = Worksheets("Log").Range("DeleteRow").Cells(2, 1).Value
strDate = Format(Date, "mm-dd-yy") & " at " & Format(Time, "h:mm:ss")

A = sh.Name
Worksheets("Log").Cells(Row, 2) = A & " deleted " & strDate

End If
End If
Next sh
 
Not tested, but perhaps after the sheet is deleted its name property cannot
be accessed?
 
Once you delete sh, it no longer exists to be referenced. Try something
like

Dim sh As Worksheet
Dim rDelLog As Range
Dim sName As String

Set rDelLog = Sheets("Log").Range("A" & Rows.Count).End(xlUp)(2, 1)
Application.DisplayAlerts = False
For Each sh In Worksheets
If sh.Range("G18").Value = 2 Then
If Worksheets.Count > 1 Then
sName = sh.Name
sh.Delete
rDelLog.Value = sName & " deleted " & _
Format(Date, "mm-dd-yy") & " at " & _
Format(Time, "h:mm:ss")
Set rDelLog = rDelLog(2, 1)
End If
End If
Next sh
 
You need to capture the sheet name before deleting it. If you delete the
sheet then refer to the object you get an automation error
Geoff
 
Back
Top