Delete Sheets if name not in approved list

[TheRedCardinal]

Hi all,

My workbook has a setup sequence that reviews all of the sheets in the workbook, and the name doesn't match a list of sheets to keep, it deletes it.

I originally did this using an IF/AND statement that checked every single sheet name (early in my coding experience) but I need the user to be able to define the sheets themselves.

So I set up a range on a main data sheet, and called it "KeepSheets"

I then tried to adapt code I found on a similar question, and it looks as follows:

For Counter = Sheets.Count To 1 Step -1

        WS2 = Application.Match(Sheets(Counter).Name, Range("KeepSheets"), 0)
        
        If IsError(WS2) Then
            WBk1.Sheets(Counter).Delete
        End If
    
    End If

Next Counter

I get "Object Variable or With Block variable not set" error on the Match section of the program.


WS2 is defined as a worksheet.

Can you see what I've done wrong?

Thanks!

 

[Fluff]

WS2 is defined as a worksheet.

Change it to a Variant.

 

[TheRedCardinal]

Change it to a Variant.

Thanks, Fluff, as ever!

I have a follow up question on this - I'll ask here but let me know if it needs a new thread.

My sheets are kept in a range - but this is awkward for the user because they would need to remember to redefine the named range each time they made an adjustment.

Better instead (and visually) would be to have a single cell, with the values stored separated by commas.

Could I do this?

 

[Fluff]

I would keep it as a range & use VBA to set the range, rather than using a named range.