I have a defined function that deletes a sheet and then logs that action to a log file:
Public Function DeleteSheet(Wks As Worksheet)
Wks.Delete
LogInformation ("deleted sheet """ & Wks.Name & """")
End Function
However, I am getting the error "Object Doesn't Support this Property or Method" When I run the following code, right in the line where the DeleteSheet function is called. What is going on?
Dim wks_del_cand As Worksheet ' worksheet delete candidate
Dim names_wks_to_del(1) As String
names_wks_to_del(0) = "Sheet1"
names_wks_to_del(1) = "Fred SPS Collat Open Exceptions"
'goes through each of the worksheets in the workbook and deletes any that match the worksheets to delete array
Dim str As Variant
For Each wks_del_cand In Wb.Worksheets
For Each str In names_wks_to_del
If wks_del_cand.Name = str Then
DeleteSheet (wks_del_cand)
Exit For
End If
Next str
Next wks_del_cand
I have even tried setting up a NEW worksheet object to pass into the function, changing
If wks_del_cand.Name = str Then
DeleteSheet (wks_del_cand)
Exit For
End If
to
If wks_del_cand.Name = str Then
Dim wks_to_del As Worksheet
Set wks_to_del = wks_del_cand
DeleteSheet (wks_to_del)
Exit For
End If
But that doesn't seem to do the trick either. Please help!
