PDA

View Full Version : is there an opposite of 'selectedsheets'



nicksinthemi
04-04-2012, 06:34 AM
Anyone know if I can do the opposite of this?

The only sheet I want to keep is always called 'Master'. Can that last command be something different to delete everything else?

Sub wkdel()
'
' wkdel Macro
'

'
Sheets("Table 1").Select
ActiveWindow.SelectedSheets.Delete
End Sub

mikerickson
04-04-2012, 06:53 AM
Perhaps something like
Dim oneSheet As Worksheet
With ThisWorkbook
If 1 < .Sheets.Count Then
.Sheets(1 - CBool(.Sheets("Master").Index = 1)).Select True

For Each oneSheet In .Sheets
If oneSheet.Name <> "Master" Then oneSheet.Select False
Next oneSheet
End If
End With


Or perhaps, you could copy Master to a new workbook, and save that workbook, deleting the old workbook that has many sheets.

mancubus
04-04-2012, 07:02 AM
or something like this.


Dim ws As Worksheet

Application.DisplayAlerts = False

For Each ws In Worksheets
If ws.Name <> "Master" Then ws.Delete
Next ws

Application.DisplayAlerts = True

nicksinthemi
04-04-2012, 07:05 AM
Many thanks this seems to work. Any idea how to get rid of the dialogue box confirming if i want to delete (t'these sheets contaion data... etc')

nicksinthemi
04-04-2012, 07:05 AM
oh, there it is. display alerts to false i guess

mancubus
04-04-2012, 07:56 AM
oh, there it is. display alerts to false i guess

:yes