selecting sheets in vb

[peterG]

I want to be able to delete all sheets from a workbook
except a few specific ones.

Is there any way to get a list of current sheets in a
form where I can delete the names I want to keep and
select the rest?

TIA
peterG

 

[Guest]

Pete

The following will loop through the sheets in the current workbook and show the names. You should be able to use this to determine the list of names you want to delete.

Ton

Sub ccc(
For Each na In ActiveWorkbook.Sheet
MsgBox na.Nam
Next n
End Sub

 

[Rob van Gelder]

PeterG,

Here's one way:

Sub testit()
Dim wks As Worksheet, arr() As Worksheet, i As Long, blnTemp As Boolean

ReDim arr(0)
For Each wks In Worksheets
Select Case wks.Name
Case "Sheet2"
Case Else
i = UBound(arr) + 1
ReDim Preserve arr(i)
Set arr(i) = wks
End Select
Next

blnTemp = Application.DisplayAlerts
Application.DisplayAlerts = False
For i = 1 To UBound(arr)
arr(i).Delete
Next
Application.DisplayAlerts = blnTemp
End Sub


Rob

 

[Doug Glancy]

Peter,

Please test this first on a test workbook or two. This will keep the sheets
you select in the userform and delete the rest. Note that you can select
groups of sheets in the userform by using the Control or Shift keys.

Create a UserForm1 with a ListBox1 and a CommandButton1 and put the code
below inside the UserForm1:

Private Sub UserForm_Initialize()

Dim sh As Worksheet

Me.ListBox1.MultiSelect = fmMultiSelectExtended

For Each sh In ActiveWorkbook.Worksheets
Me.ListBox1.AddItem (sh.Name)
Next sh

End Sub

Private Sub CommandButton1_Click()

Dim sheets_selected, delete_sheets As Boolean
Dim i As Integer

sheets_selected = False
delete_sheets = False

With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) = True Then
sheets_selected = True
Exit For
End If
Next i
End With

If sheets_selected Then
delete_sheets = MsgBox(prompt:="Selected Sheets Will Be Kept" & vbCrLf &
_
"Those NOT Selected Will be DELETED", _
Buttons:=vbOKCancel + vbExclamation, _
Title:="Delete UnSelected Sheets") - 2
If delete_sheets Then
With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) = False Then
Application.DisplayAlerts = False
ActiveWorkbook.Worksheets(.List(i)).Delete
Application.DisplayAlerts = True
End If
Next i
End With
End If
End If

Unload Me

End Sub

Call the form in a regular module with:

UserForm1.Show

hth,

Doug

 

[Tim Zych]

Here's a macro that displays a list of the worksheets in the workbook.
Select the sheets, click the button and they will be removed. Deleting
sheets is not a reversible action so save a backup workbook.

Create a userform with a listbox and command button, UserForm1, ListBox1,
CommandButton1. Code goes in the form.

Private Sub UserForm_Initialize()
Call RefreshList
CommandButton1.Caption = "Delete Selection"
ListBox1.MultiSelect = fmMultiSelectExtended
End Sub

Sub RefreshList()
ListBox1.Clear
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
ListBox1.AddItem wks.Name
Next
End Sub

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
If MsgBox("Warning. This will remove the selected sheets. Continue?",
vbYesNo) = _
vbYes Then
Application.DisplayAlerts = False
ThisWorkbook.Worksheets(ListBox1.List(i)).Delete
Application.DisplayAlerts = True
End If
End If
Next i
Call RefreshList
End Sub

 

[Tim Zych]

Correction. Use this one instead. Last one's message was misplaced.


Private Sub UserForm_Initialize()
Call RefreshList
CommandButton1.Caption = "Delete Selection"
ListBox1.MultiSelect = fmMultiSelectExtended
End Sub

Sub RefreshList()
ListBox1.Clear
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
ListBox1.AddItem wks.Name
Next
End Sub

Private Sub CommandButton1_Click()
If MsgBox("Warning. This will remove the selected sheets. Continue?", _
vbYesNo) = vbNo Then Exit Sub
Dim i As Integer
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
Application.DisplayAlerts = False
ThisWorkbook.Worksheets(ListBox1.List(i)).Delete
Application.DisplayAlerts = True
End If
Next i
Call RefreshList
End Sub

 

[Bob Phillips]

Slightly updated version of Tim's code in case they try and delete all
sheets gives a warning and exits

Option Explicit

Private Sub UserForm_Initialize()
Call RefreshList
CommandButton1.Caption = "Delete Selection"
ListBox1.MultiSelect = fmMultiSelectExtended
End Sub

Sub RefreshList()
ListBox1.Clear
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
ListBox1.AddItem wks.Name
Next
End Sub

Private Sub CommandButton1_Click()
Dim i As Integer
Dim fAllSelected As Boolean

fAllSelected = True
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = False Then
Exit For
End If
Next i

If fAllSelected Then
MsgBox "You cannot delete all of the sheets", vbExclamation
Exit Sub
End If

If MsgBox("Warning. This will remove the selected sheets. Continue?", _
vbYesNo) = vbNo Then Exit Sub

For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
Application.DisplayAlerts = False
ThisWorkbook.Worksheets(ListBox1.List(i)).Delete
Application.DisplayAlerts = True
End If
Next i
Call RefreshList
End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)