VBA: Deleting all sheets but one, not knowing sheet names

[Beowulf]

I have to do some data cleaning of different files where every sheet has its unique name.
The sheet with the macrobutton should be saved, the rest of the sheets deleted. Why doesn't the following work:

<code>
Dim ws As Worksheet
dim MADDATA as Worksheet
Set MADDATA = ActiveSheet
Application.DisplayAlerts = False
For Each ws In Worksheets
If ws <> MADDATA Then
ws.Delete
End If
Next
Application.DisplayAlerts = True
</code>

Help would be much appreciated!

 

[schielrn]

I believe you need to declare what you are trying to compare rather than just comparing the objects? Try:

Dim ws As Worksheet
dim MADDATA as Worksheet
Set MADDATA = ActiveSheet
Application.DisplayAlerts = False
For Each ws In Worksheets
If ws.name <> MADDATA.name Then
ws.Delete
End If
Next
Application.DisplayAlerts = True

Hope that works.

 

[schielrn]

Not sure why this is showing up in two code boxes when I only want one and I even edited to remove the extra code box?

 

[Beowulf]

Dearest schielrn!

Wow! It took the whole of 10 seconds for you to assess and address my problem. My sincere thanks!