Hide and delete sheets based on cell input

[kokjinq]

Hi,

I am fairly new in vba.

I have a situation where i would like to hide and delete a few sheets in my worksheet.

there is a few sheets where i want it to be constantly visible and one sheet is based on a cell reference.

the remaining irrelevant sheets i would like it to be hidden and deleted.

How do i go about this?

'always visible are sheets that are constantly visible in all files'
'visible msds is the sheet where my product data is being pulled out based on a input i enter on my first sheet'
the sheets i want to hide and delete will be the remaining irrelevant "MSDS" that i would like to remove through macro.

Sub hide()

'always visible'

Worksheets("Input").Visible = xlSheetVisible
Worksheets("Check sheet").Visible = xlSheetVisible
Worksheets("Component Description").Visible = xlSheetVisible

'visible msds'

Dim rng As Range
Dim wks As Worksheet

Set rng = Sheets("Input").Range("B3")
Set wks = Sheets(rng.Value)
wks.Visible = xlSheetVisible

Thanks guys,

Hopefully you all can help me in this.

[alansidman]

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

[alansidman]

Try this:

Option Explicit
Sub hide()

'always visible'

Worksheets("Input").Visible = xlSheetVisible
Worksheets("Check sheet").Visible = xlSheetVisible
Worksheets("Component Description").Visible = xlSheetVisible

'visible msds'

Dim rng As Range
Dim wks As Worksheet

Set rng = Sheets("Input").Range("B3")
Set wks = Sheets(rng.Value)
wks.Visible = xlSheetVisible

Application.DisplayAlerts = False

For Each wks In Worksheets
    If wks.Name <> "Input" And wks.Name <> "Check sheet" And wks.Name <> "Componwent Description" And wks.Name <> rng Then
        wks.Delete
    End If
Next wks
Application.DisplayAlerts = True
End Sub

[kokjinq]

Hi alansidman,

That worked like a charm.

Thanks a lot for the help.