Deleting All Sheets Other than specified from a workbook
 

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

--
Message posted from http://www.ExcelForum.com

Deleting All Sheets Other than specified from a workbook
 

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

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 all
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
a
script I already know :(

Any help would be marvelous


All the best

Nic


---
Message posted from http://www.ExcelForum.com/

Deleting All Sheets Other than specified from a workbook
 

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


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Nic@Rolls-Royce " wrote in message
...
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 all
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 a
script I already know :(

Any help would be marvelous


All the best

Nic


---
Message posted from http://www.ExcelForum.com/

Deleting All Sheets Other than specified from a workbook
 

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


--
Regards,
Tom Ogilvy


"Frank Kabel" wrote in message
...
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

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 all
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
a
script I already know :(

Any help would be marvelous


All the best

Nic


---
Message posted from http://www.ExcelForum.com/

Deleting All Sheets Other than specified from a workbook
 

Hi Tom
good point!
Frank

Deleting All Sheets Other than specified from a workbook
 

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..)

"Frank Kabel" wrote in message
...
Hi Tom
good point!
Frank

Deleting All Sheets Other than specified from a workbook
 

Hi
using TRIM and Lcase in the worksheet name comparison. This way a
worksheet name ' PROCESS SHEET' will match to 'process sheet'
Frank


JMay wrote:
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..)

"Frank Kabel" wrote in message
...
Hi Tom
good point!
Frank

Deleting All Sheets Other than specified from a workbook
 

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

--
Message posted from http://www.ExcelForum.com

Deleting All Sheets Other than specified from a workbook
 

Sorry to clarify, it is only to selct a sheet in a workbook afte
running the scrip

--
Message posted from http://www.ExcelForum.com

Deleting All Sheets Other than specified from a workbook
 

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

--
Regards,
Tom Ogilvy


"Nic@Rolls-Royce " wrote in
message ...
Sorry to clarify, it is only to selct a sheet in a workbook after
running the script


---
Message posted from http://www.ExcelForum.com/

Deleting All Sheets Other than specified from a workbook
 

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

Excellent thankyou..

One small question

why does this not work?

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

Am I missing something?


NIc


---
Message posted from http://www.ExcelForum.com/