Deleting a sheet via code-- different criteria

[KimberlyC]

Hi Again...

I've thought of a better way to remove unwanted worksheets from my
workbook....

I need to run code that will delete all worksheets in my activeworkbook that
have "Delete" in cell A3 of the worksheet.
Cell A3 has a formula in it of each worksheet that determines if the sheet
needs to be deleted or not... if it needs to be deleted.. then, the formula
returns "delete"...otherwise..it's blank "".


I think this will work better than my previous post......


I'm not sure how to do this ... so any help is greatly appreciated..

Thanks in advance,
Kimberly

[Kim]

sub test
dim sht as sheet
for each sht in activeworkbook
if sht.range("A3")="delete" then
sht.delete
end if
next
end sub


"KimberlyC" <[email protected]> wrote in message
news:[email protected]...
> Hi Again...
>
> I've thought of a better way to remove unwanted worksheets from my
> workbook....
>
> I need to run code that will delete all worksheets in my activeworkbook
that
> have "Delete" in cell A3 of the worksheet.
> Cell A3 has a formula in it of each worksheet that determines if the sheet
> needs to be deleted or not... if it needs to be deleted.. then, the
formula
> returns "delete"...otherwise..it's blank "".
>
>
> I think this will work better than my previous post......
>
>
> I'm not sure how to do this ... so any help is greatly appreciated..
>
> Thanks in advance,
> Kimberly
>
>

[Bob Phillips]

Don't forget to set

Application.DisplayALerts = False

to avoid the warning, and reset at the end.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Kim" <[email protected]> wrote in message
news:[email protected]...
> sub test
> dim sht as sheet
> for each sht in activeworkbook
> if sht.range("A3")="delete" then
> sht.delete
> end if
> next
> end sub
>
>
> "KimberlyC" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Again...
> >
> > I've thought of a better way to remove unwanted worksheets from my
> > workbook....
> >
> > I need to run code that will delete all worksheets in my activeworkbook
> that
> > have "Delete" in cell A3 of the worksheet.
> > Cell A3 has a formula in it of each worksheet that determines if the
sheet
> > needs to be deleted or not... if it needs to be deleted.. then, the
> formula
> > returns "delete"...otherwise..it's blank "".
> >
> >
> > I think this will work better than my previous post......
> >
> >
> > I'm not sure how to do this ... so any help is greatly appreciated..
> >
> > Thanks in advance,
> > Kimberly
> >
> >
>
>

[Piranha]

Kim,
I tried your code and it would stop at the second and third line.
I had to change them to make it work. After i changed them it worked fine.
My question is:
Why and what is the difference, between the two statements?
What is the difference between Sheet and Worksheet?

The red is what i changed them to.
sub test
'dim sht as sheet
Dim sht As Worksheet
'for each sht in activeworkbook
For Each sht In ActiveWorkbook.Worksheets
if sht.range("A3")="delete" then
sht.delete
end if
next

Thx
Dave

sub test
dim sht as sheet
for each sht in activeworkbook
if sht.range("A3")="delete" then
sht.delete
end if
next
end sub


"KimberlyC" <[email protected]> wrote in message
news:[email protected]...
> Hi Again...
>
> I've thought of a better way to remove unwanted worksheets from my
> workbook....
>
> I need to run code that will delete all worksheets in my activeworkbook
that
> have "Delete" in cell A3 of the worksheet.
> Cell A3 has a formula in it of each worksheet that determines if the sheet
> needs to be deleted or not... if it needs to be deleted.. then, the
formula
> returns "delete"...otherwise..it's blank "".
>
>
> I think this will work better than my previous post......
>
>
> I'm not sure how to do this ... so any help is greatly appreciated..
>
> Thanks in advance,
> Kimberly
>
>

[STEVE BELL]

I got caught by this the other day -

Sheets includes worksheets and chart sheets

Worksheets doesn't include chart sheets.
--
steveB

Remove "AYN" from email to respond
"Piranha" <[email protected]> wrote in
message news:[email protected]...
>
> Kim,
> I tried your code and it would stop at the second and third line.
> I had to change them to make it work. After i changed them it worked
> fine.
> My question is:
> Why and what is the difference, between the two statements?
> What is the difference between Sheet and Worksheet?
>
> The red is what i changed them to.
> sub test
> 'dim sht as sheet
> Dim sht As Worksheet
> 'for each sht in activeworkbook
> For Each sht In ActiveWorkbook.Worksheets
> if sht.range("A3")="delete" then
> sht.delete
> end if
> next
>
> Thx
> Dave
> Kim Wrote:
>> sub test
>> dim sht as sheet
>> for each sht in activeworkbook
>> if sht.range("A3")="delete" then
>> sht.delete
>> end if
>> next
>> end sub
>>
>>
>> "KimberlyC" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi Again...
>> >
>> > I've thought of a better way to remove unwanted worksheets from my
>> > workbook....
>> >
>> > I need to run code that will delete all worksheets in my
>> activeworkbook
>> that
>> > have "Delete" in cell A3 of the worksheet.
>> > Cell A3 has a formula in it of each worksheet that determines if the
>> sheet
>> > needs to be deleted or not... if it needs to be deleted.. then, the
>> formula
>> > returns "delete"...otherwise..it's blank "".
>> >
>> >
>> > I think this will work better than my previous post......
>> >
>> >
>> > I'm not sure how to do this ... so any help is greatly appreciated..
>> >
>> > Thanks in advance,
>> > Kimberly
>> >
>> >
>
>
> --
> Piranha
> ------------------------------------------------------------------------
> Piranha's Profile:
> http://www.excelforum.com/member.php...o&userid=20435
> View this thread: http://www.excelforum.com/showthread...hreadid=386308
>