tiredofit
Well-known Member
- Joined
- Apr 11, 2013
- Messages
- 1,834
- Office Version
- 365
- 2019
- Platform
- Windows
I want to delete worksheets according to what's specified on a worksheet.
In Sheet1, I have a named range called b, which contains two values, Sheet1 and Sheet2.
This is in Class1:
The code fails here:
with a message:
What is wrong?
Thanks
EDIT SORTED:
In Sheet1, I have a named range called b, which contains two values, Sheet1 and Sheet2.
Code:
Option Explicit
Sub Start()
Dim MyCls As Class1
Set MyCls = New Class1
Set MyCls.Cols = Sheet1.Range("b")
Call MyCls.DeleteWorksheets
End Sub
This is in Class1:
Code:
Option Explicit
Private pCols As Range
Public Property Get Cols() As Range
Set Cols = pCols
End Property
Public Property Set Cols(ByVal C As Range)
Set pCols = C
End Property
Sub DeleteWorksheets()
Application.EnableEvents = False
Dim Rng As Range
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
With Sheet1(Cols)
Set Rng = .Find(What:=ws.Name, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Rng Is Nothing Then
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End If
End With
Next ws
Application.EnableEvents = True
End Sub
The code fails here:
Code:
With Sheet1(Cols)
with a message:
Code:
Run-time error '438': Object doesn't support this property or method
What is wrong?
Thanks
EDIT SORTED:
Code:
With Cols
Last edited: