Automation Error?

[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

 

[Vasant Nanavati]

Not tested, but perhaps after the sheet is deleted its name property cannot
be accessed?

 

[JE McGimpsey]

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

 

[Geoff Kelly]

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