Deleting All Sheets Other than specified from a workbook

  • Thread starter Thread starter Nic@Rolls-Royce
  • Start date Start date
N

Nic@Rolls-Royce

Hello :)

I run a vba script to populate and format another workbook with data.

I would like to add to this script a clever bit of code to remove al
sheets other that a set sheet

i.e remove all sheets except 'process sheet'

unfortunatly 'process sheet' is not the active sheet so I cannot use
script I already know :(

Any help would be marvelous


All the best

Ni
 
Hi nic
one way: try

Public Sub delete_sheets()
Dim wkSht As Worksheet
Application.DisplayAlerts = False
For Each wkSht In Worksheets
If wkSht.Name <> "process sheet" Then
wkSht.Delete
End If
Next wkSht
Application.DisplayAlerts = True
End Sub

Frank
 
Hi Nic

Try this one

Sub test()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> "process sheet" Then
Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True
End If
Next sh
End Sub
 
A possible addition that might be advisable:

Public Sub delete_sheets()
Dim wkSht As Worksheet
Application.DisplayAlerts = False
For Each wkSht In Worksheets
If Lcase(trim(wkSht.Name)) <> "process sheet" Then
wkSht.Delete
End If
Next wkSht
Application.DisplayAlerts = True
End Sub

or

Public Sub delete_sheets()
Dim wkSht As Worksheet
Application.DisplayAlerts = False
For Each wkSht In Worksheets
If wkSht.Name <> worksheet("process sheet").Name Then
wkSht.Delete
End If
Next wkSht
Application.DisplayAlerts = True
End Sub
 
What is the point? daaaaaaaaaaaa.............
(It's not obvious to the s-l-o-w--l-e-a-r-n-e-r-s, out here..)
 
Hi
using TRIM and Lcase in the worksheet name comparison. This way a
worksheet name ' PROCESS SHEET' will match to 'process sheet'
Frank
 
Excellent thankyou..

One small question

why does this not work?

Workbooks("Excel Matrix Database - A3 Copy Of Gates + Ful
Matrix.xls").Worksheets("GateA").Select

Am I missing something?


NI
 
Sorry to clarify, it is only to selct a sheet in a workbook afte
running the scrip
 
If the workbook is not the activeworkbook, then you can not select a sheet
in that workbook. You may have to do it in two steps.

Workbooks("Excel Matrix Database - A3 Copy Of Gates + Full
Matrix.xls").Activate

Worksheets("GateA").Activate

' then if you wanted to select a range
Range("B9").Select
 
Hi
you have to activate the workbook and worksheet first.
Try
Workbooks("Excel Matrix Database - A3 Copy Of Gates + Full
Matrix.xls").Activate
Worksheets("GateA").Activate
Range("GateA").select

Frank
 
Back
Top