VBA – Loop through sheets

This code snippet can be used if you want to loop thorugh the sheets in your workbook, either because you want to add something to everysheet, or – as in the example – you want to delete sheets with a specific name.

Sub slet_Faner()
Application.DisplayAlerts = False ' Makes it unnecessary for the user to approve the deletion

Dim ws As Worksheet
   For Each ws In Worksheets
           If ws.Name = "Home" Then ws.Delete 'Delete if name of sheet is "Home"
    Next

Application.DisplayAlerts = True
End Sub

Of course, if you want all sheets BUT the one sheet with a specific name, you just use:

Dim ws As Worksheet
   For Each ws In Worksheets
      If ws.Name <> "Home" Then ws.Delete 'Delete if name of sheet ISN'T "Home"
    Next

Wednesday, December 8th, 2010 VBA