deleting work-sheets after a certain number

[sieger007]

Hi Folks
I get a report that has about 17 worksheets . I'd like to keep just the 1st three worksheets. None of the worksheets are hardcoded in their name. e.g. the 1st worksheet by default would be sheet1 but its say <date>.sheet1.
I'd like to have a macro that'll delete ALL the worksheets except the 1st three.
Thanks
Sam

 

[VoG]

Try this

Sub DeleteSheets()
Dim i As Long
Application.DisplayAlerts = False
For i = Worksheets.Count To 4 Step -1
    Worksheets(i).Delete
Next i
Application.DisplayAlerts = True
End Sub

 

[Trebor76]

Hi there,

Try the following which will delete any tab(s) past the third i.e. the first three from left to right including any hidden tab(s) will not be deleted:

Sub DeleteAllButFirstThree()

    'http://www.mrexcel.com/forum/showthread.php?t=560698
    
    Dim strArray() As String 'Declares a dynamic array variable
    Dim intArrayCount As Integer
    
    If ActiveWorkbook.Worksheets.Count < 4 Then
        MsgBox "There are only " & ActiveWorkbook.Worksheets.Count & " tabs in the workbook!!", vbExclamation, "Delete tab Editor"
        Exit Sub
    End If
    
    intArrayCount = 0
    
    Application.ScreenUpdating = False
    
    For Each Worksheet In ActiveWorkbook.Worksheets
        If Worksheet.Index > 3 Then
            intArrayCount = intArrayCount + 1
            ReDim Preserve strArray(1 To intArrayCount) 'Copy elements from the existing array to the new array
            strArray(intArrayCount) = Worksheet.Name
        End If
    Next
    
    Application.DisplayAlerts = False
        ActiveWorkbook.Worksheets(strArray).Delete
    Application.DisplayAlerts = True
    
    Erase strArray() 'Deletes the varible contents to free some memory
    
    Application.ScreenUpdating = True
    
End Sub

Note, as the code will delete tabs without asking any questions, make sure you initially run the macro on a copy of your workbook in case the results are not as expected.

Regards,

Robert