Deleting all sheets and Chart sheets except specifically named sheets I would li

Question

Deleting all sheets and Chart sheets except specifically named sheets

I would like a code that will delete all Chart sheets and sheets (with various name formats) except sheets named "Sheet1", "Table" and "SurfGraph". And then clear all data in "Sheet1" but leave the data in "Table" and "SurfGraph".

I have some sort of code that seems to do most of this, however, it does not delete the Chart sheets which I need it to as well as just removing generic worksheets.

Sub DeleteData()

Dim Message As String

Dim Sure As Integer

Message = "Are you sure you want to delete all data in this workbook?"

Sure = MsgBox(Message, vbOKCancel)

If Sure = 1 Then Call DeleteSheets

Call DeleteImport

End Sub

Sub DeleteSheets()

Dim ws As Worksheet

Application.DisplayAlerts = False

For Each ws In Worksheets

    Select Case ws.Name

        Case "Sheet1" < would like this to include "Table" and "SurfGraph"

            ' Do nothing

        Case Else

            ws.Delete

    End Select

Next ws

Application.DisplayAlerts = True

End Sub

Sub DeleteImport()

Application.DisplayAlerts = False

Sheets("Sheet1").Select

Columns("A:L").Select

Selection.Delete Shift:=xlToLeft

Range("A1").Select

^ would just like the small bold part above to just clear data rather than delete columns

Application.DisplayAlerts = True

End Sub

Thanks for help!!

Explanation / Answer

Hi..

I would do it this way. Note this amount of deletion is pretty drastic so test on a copy of your workbook.

Sub somesub()
Dim v As Variant
Dim s As String
Dim Sh
Application.DisplayAlerts = False
s = "Sheet1,Table,SurfGraph"
v = Split(s, ",")
For Each Sh In ThisWorkbook.Sheets
If IsError(Application.Match(CStr(Sh.Name), v, 0)) Then
    Sh.Delete
    End If
Next Sh
Application.DisplayAlerts = True
Sheets("Sheet1").Range("A:L").ClearContentss
End Sub

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.