Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting All Sheets Other than specified from a workbook
Hi Tom
good point! Frank |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
deleting sheets 2&3 | Excel Discussion (Misc queries) | |||
deleting sheets | Excel Discussion (Misc queries) | |||
Deleting sheets | Excel Programming | |||
Deleting duplicates across sheets p. 2 | Excel Programming | |||
deleting sheets | Excel Programming |