Looping through sheets
 

I'm trying to go throug all sheets in the active workbook and delete some of
them based on the sheet name. This is what i have so far:

Dim WSS As Worksheet
For Each WSS In ActiveWorkbook
MsgBox "now " & WSS.Name & " will be deleted"
If Right(WSS.Name, 2) = "_1" Then WSS.Delete
Next

I get an error about the "ActiveWorkbook" in line two.

Anyone able to help me out?
The Doctor

Looping through sheets
 

Sub marine()
Dim WSS As Worksheet
For Each WSS In ActiveWorkbook.Sheets
MsgBox "now " & WSS.Name & " will be deleted"
If Right(WSS.Name, 2) = "_1" Then WSS.Delete
Next

End Sub


Note that the code does not die, but the message is issued whether the sheet
is deleted or not.
--
Gary''s Student - gsnu200908


"Dr. Schwartz" wrote:

I'm trying to go throug all sheets in the active workbook and delete some of
them based on the sheet name. This is what i have so far:

Dim WSS As Worksheet
For Each WSS In ActiveWorkbook
MsgBox "now " & WSS.Name & " will be deleted"
If Right(WSS.Name, 2) = "_1" Then WSS.Delete
Next

I get an error about the "ActiveWorkbook" in line two.

Anyone able to help me out?
The Doctor

Looping through sheets
 

Thanks Gary!

Do you also know this then? When the sheet is deleted i'm prompted with a
message saying all data in the sheet will be lost. I know that it is possible
to bypass (not show) this message using code but i can not remember how...

Do you or anyone else know this?
Thanks / The Doctor

"Gary''s Student" wrote:

Sub marine()
Dim WSS As Worksheet
For Each WSS In ActiveWorkbook.Sheets
MsgBox "now " & WSS.Name & " will be deleted"
If Right(WSS.Name, 2) = "_1" Then WSS.Delete
Next

End Sub


Note that the code does not die, but the message is issued whether the sheet
is deleted or not.
--
Gary''s Student - gsnu200908


"Dr. Schwartz" wrote:

I'm trying to go throug all sheets in the active workbook and delete some of
them based on the sheet name. This is what i have so far:

Dim WSS As Worksheet
For Each WSS In ActiveWorkbook
MsgBox "now " & WSS.Name & " will be deleted"
If Right(WSS.Name, 2) = "_1" Then WSS.Delete
Next

I get an error about the "ActiveWorkbook" in line two.

Anyone able to help me out?
The Doctor

Looping through sheets
 

Application.DisplayAlerts=False
--
Gary''s Student - gsnu200908


"Dr. Schwartz" wrote:

Thanks Gary!

Do you also know this then? When the sheet is deleted i'm prompted with a
message saying all data in the sheet will be lost. I know that it is possible
to bypass (not show) this message using code but i can not remember how...

Do you or anyone else know this?
Thanks / The Doctor

"Gary''s Student" wrote:

Sub marine()
Dim WSS As Worksheet
For Each WSS In ActiveWorkbook.Sheets
MsgBox "now " & WSS.Name & " will be deleted"
If Right(WSS.Name, 2) = "_1" Then WSS.Delete
Next

End Sub


Note that the code does not die, but the message is issued whether the sheet
is deleted or not.
--
Gary''s Student - gsnu200908


"Dr. Schwartz" wrote:

I'm trying to go throug all sheets in the active workbook and delete some of
them based on the sheet name. This is what i have so far:

Dim WSS As Worksheet
For Each WSS In ActiveWorkbook
MsgBox "now " & WSS.Name & " will be deleted"
If Right(WSS.Name, 2) = "_1" Then WSS.Delete
Next

I get an error about the "ActiveWorkbook" in line two.

Anyone able to help me out?
The Doctor

Looping through sheets
 

Thats the one! Thanks :-)

"Gary''s Student" wrote:

Application.DisplayAlerts=False
--
Gary''s Student - gsnu200908


"Dr. Schwartz" wrote:

Thanks Gary!

Do you also know this then? When the sheet is deleted i'm prompted with a
message saying all data in the sheet will be lost. I know that it is possible
to bypass (not show) this message using code but i can not remember how...

Do you or anyone else know this?
Thanks / The Doctor

"Gary''s Student" wrote:

Sub marine()
Dim WSS As Worksheet
For Each WSS In ActiveWorkbook.Sheets
MsgBox "now " & WSS.Name & " will be deleted"
If Right(WSS.Name, 2) = "_1" Then WSS.Delete
Next

End Sub


Note that the code does not die, but the message is issued whether the sheet
is deleted or not.
--
Gary''s Student - gsnu200908


"Dr. Schwartz" wrote:

I'm trying to go throug all sheets in the active workbook and delete some of
them based on the sheet name. This is what i have so far:

Dim WSS As Worksheet
For Each WSS In ActiveWorkbook
MsgBox "now " & WSS.Name & " will be deleted"
If Right(WSS.Name, 2) = "_1" Then WSS.Delete
Next

I get an error about the "ActiveWorkbook" in line two.

Anyone able to help me out?
The Doctor