[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Delete sheets - all except 3

joecrabtree

12/19/2006 9:47:00 AM

To all,

I have a workbook with many sheets, and wish to delete all of them
apart from the following 3:

WELCOME, ImportedRawData, + ImportedDataEdit


How can I do this?

Thanks very much for your help in advance,

Regards

Joseph Crabtree
4 Answers

Bob Phillips

12/19/2006 9:59:00 AM

0

Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "WELCOME" And _
ws.Name <> "ImportedRawData" And _
ws.Name <> "ImportedDataEdit" Then
ws.DisplayPageBreaks
End If
Next ws
Application.DisplaAlerts = True

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"joecrabtree" <thejoecrabtree@gmail.com> wrote in message
news:1166521607.775633.123520@n67g2000cwd.googlegroups.com...
> To all,
>
> I have a workbook with many sheets, and wish to delete all of them
> apart from the following 3:
>
> WELCOME, ImportedRawData, + ImportedDataEdit
>
>
> How can I do this?
>
> Thanks very much for your help in advance,
>
> Regards
>
> Joseph Crabtree
>


an01digital

12/19/2006 10:01:00 AM

0



Hi Joseph,

Try this...

sub test()

dim sheet as object

for each sheet in activeworkbook.sheets

if sheet.name<>"WELCOME" and sheet.name<>"ImportedRawData" and
sheet.name<>"ImportedDataEdit" then

sheet.delete

end if

next sheet
End sub

Regards
Ankur
www.xlmacros.com


On Dec 19, 2:46 pm, "joecrabtree" <thejoecrabt...@gmail.com> wrote:
> To all,
>
> I have a workbook with many sheets, and wish to delete all of them
> apart from the following 3:
>
> WELCOME, ImportedRawData, + ImportedDataEdit
>
> How can I do this?
>
> Thanks very much for your help in advance,
>
> Regards
>
> Joseph Crabtree

Dave Peterson

12/19/2006 4:06:00 PM

0

One more...

Dim wks As Worksheet
Application.DisplayAlerts = False
For Each wks In ActiveWorkbook.Worksheets
select case lcase(wks.name)
case is = "welcome", "importedrawdata","importeddataedit"
'do nothing
case else
wks.delete
end select
next wks
Application.DisplaAlerts = True

Since the "select case" is looking for lower case characters (lcase), make sure
you type those strings in lower case.



joecrabtree wrote:
>
> To all,
>
> I have a workbook with many sheets, and wish to delete all of them
> apart from the following 3:
>
> WELCOME, ImportedRawData, + ImportedDataEdit
>
> How can I do this?
>
> Thanks very much for your help in advance,
>
> Regards
>
> Joseph Crabtree

--

Dave Peterson

Dave Peterson

12/19/2006 4:07:00 PM

0

Autocomplete is too quick!

ws.displaypagebreaks
should be
ws.delete

<vbg>

Bob Phillips wrote:
>
> Dim ws As Worksheet
> Application.DisplayAlerts = False
> For Each ws In ActiveWorkbook.Worksheets
> If ws.Name <> "WELCOME" And _
> ws.Name <> "ImportedRawData" And _
> ws.Name <> "ImportedDataEdit" Then
> ws.DisplayPageBreaks
> End If
> Next ws
> Application.DisplaAlerts = True
>
> --
> ---
> HTH
>
> Bob
>
> (change the xxxx to gmail if mailing direct)
>
> "joecrabtree" <thejoecrabtree@gmail.com> wrote in message
> news:1166521607.775633.123520@n67g2000cwd.googlegroups.com...
> > To all,
> >
> > I have a workbook with many sheets, and wish to delete all of them
> > apart from the following 3:
> >
> > WELCOME, ImportedRawData, + ImportedDataEdit
> >
> >
> > How can I do this?
> >
> > Thanks very much for your help in advance,
> >
> > Regards
> >
> > Joseph Crabtree
> >

--

Dave Peterson