Macro for deleting all worksheets after 1st three

**sieger007** · 06-28-2011, 06:00 AM

Hi Folks
I want to just keep the 1st 3 worksheets in an excel sheet ( they are variably named e.g. 23,12,34 ....or 1,2,3 etc ) and delete the rest .
Any pointers are appreciated.
Thx
S

**royUK** · 06-28-2011, 06:44 AM

The problem that will arise is if the sheets are moved by someone

**sieger007** · 06-28-2011, 08:36 AM

Originally Posted by royUK

The problem that will arise is if the sheets are moved by someone

Hi Thx
Not sure what is meant. I just wanted a simple macro, that can delete all worksheets from an Excel file , except the 1st three. The total # of work-sheets can vary but the deletes need to automatically happen after the 3rd worksheet. The 1st 3 worksheets can have variable names and not the standard sheet 1 , sheet 2 names

**royUK** · 06-29-2011, 03:03 AM

If a user moves the position of any of the first three sheets say #2 to the fourth position or add sheets before sheet #1, this will change the sheets that are deleted

Option Explicit

Sub DeleteShts()
    Dim iX As Integer
    Dim Shts As Integer

    Shts = ThisWorkbook.Sheets.Count
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        On Error GoTo exit_proc
        For iX = Shts To 4 Step -1
            Sheets(iX).Delete
        Next iX
exit_proc:
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    On Error GoTo 0
End Sub

**docMed** · 06-29-2011, 03:14 AM

You could use custom document properties to store the initial names of the sheets you want to keep and use something like...

Sub DeleteSheets()
wkscnt = ActiveWorkbook.Sheets.Count
CheckSheets:
If wkscnt > 0 Then
If ActiveWorkbook.Sheets(wkscnt).Name = ActiveWorkbook.CustomDocumentProperties("SheetName1") _
Or ActiveWorkbook.Sheets(wkscnt).Name =  ActiveWorkbook.CustomDocumentProperties("SheetName2") _
Or ActiveWorkbook.Sheets(wkscnt).Name =  ActiveWorkbook.CustomDocumentProperties("SheetName3") Then
wkscnt = wkscnt - 1
Else
Excel.Application.DisplayAlerts = False
ActiveWorkbook.Sheets(wkscnt).Delete
Excel.Application.DisplayAlerts = True
End If
GoTo CheckSheets
End If

End Sub

You could also hide the sheet names somewhere on the spreadsheet and simply reference them using Cells(row value of cell, column value of cell).Value instead of custom document properties.

If you want to know how to create custom document properties, just simply google it =)

Good luck

~docMed

**snb** · 06-29-2011, 03:20 AM

or

sub snb()
  application.displayalerts=false
  do until thisworkbook.worksheets.count=3
    thisworkbook.worksheets(thisworkbook.worksheets.count).delete
  loop
end sub

**royUK** · 06-29-2011, 06:11 AM

Storing the names wouldn't work if the user changed the names

**docMed** · 06-29-2011, 09:59 AM

Originally Posted by royUK

Storing the names wouldn't work if the user changed the names

you could use Workbook_NewSheet(ByVal Sh As Object) to detect up to 2 additional (since I'm going to assume there's at least 1 worksheet already) and capture their names by forcing them to name the worksheet any time a new sheet is created. Use a custom doc property integer to just count up to 3, and once that is satisfied, disable the workbook_newsheet command.

Only issue is then how to capture additional name changes to the original worksheets...

However, if the user simply created a worksheet each time, was prompted for the name immediately on each creation, and didn't change the name again, then... it could work - lol.