Repeat a command in a Macro
 

I have a macro that simply looks like this:

Sheets("Template").Select
ActiveSheet.Unprotect
ActiveSheet.Next.Select
ActiveSheet.Unprotect
ActiveSheet.Next.Select
ActiveSheet.Unprotect
etc................

I have 72 sheets, and don't want to keep having to go in and paste in more
lines everytime I add a new sheet.
Is there someway to tell it to repeat the Select & Unprotect lines x number
of times, which would give a MUCH shorter code! Or, maybe there is a way to
tell it to keep repeating that until it gets to the last sheet in the
workbook ???

Phil

--
http://www.redbrick.dcu.ie/~pele

Hi Phil,

You can use loops like this one:

For each ws in ActiveWorkbook.Worksheets
ws.Unprotect
Next ws

Regards,
KL


"Phil Osman" wrote in message
...
I have a macro that simply looks like this:

Sheets("Template").Select
ActiveSheet.Unprotect
ActiveSheet.Next.Select
ActiveSheet.Unprotect
ActiveSheet.Next.Select
ActiveSheet.Unprotect
etc................

I have 72 sheets, and don't want to keep having to go in and paste in more
lines everytime I add a new sheet.
Is there someway to tell it to repeat the Select & Unprotect lines x
number
of times, which would give a MUCH shorter code! Or, maybe there is a way
to
tell it to keep repeating that until it gets to the last sheet in the
workbook ???

Phil

--
http://www.redbrick.dcu.ie/~pele

That works, but is there a way to modify the code so that it only starts from
SheetX and continues to the end of the workbook ?

--
http://www.redbrick.dcu.ie/~pele


"KL" wrote:

Hi Phil,

You can use loops like this one:

For each ws in ActiveWorkbook.Worksheets
ws.Unprotect
Next ws

Regards,
KL


"Phil Osman" wrote in message
...
I have a macro that simply looks like this:

Sheets("Template").Select
ActiveSheet.Unprotect
ActiveSheet.Next.Select
ActiveSheet.Unprotect
ActiveSheet.Next.Select
ActiveSheet.Unprotect
etc................

I have 72 sheets, and don't want to keep having to go in and paste in more
lines everytime I add a new sheet.
Is there someway to tell it to repeat the Select & Unprotect lines x
number
of times, which would give a MUCH shorter code! Or, maybe there is a way
to
tell it to keep repeating that until it gets to the last sheet in the
workbook ???

Phil

--
http://www.redbrick.dcu.ie/~pele

Try this:

For i=5 To Worksheets.Count
Worksheets(i).Unprotect
Next i

KL

"Phil Osman" wrote in message
...
That works, but is there a way to modify the code so that it only starts
from
SheetX and continues to the end of the workbook ?

--
http://www.redbrick.dcu.ie/~pele


"KL" wrote:

Hi Phil,

You can use loops like this one:

For each ws in ActiveWorkbook.Worksheets
ws.Unprotect
Next ws

Regards,
KL


"Phil Osman" wrote in message
...
I have a macro that simply looks like this:

Sheets("Template").Select
ActiveSheet.Unprotect
ActiveSheet.Next.Select
ActiveSheet.Unprotect
ActiveSheet.Next.Select
ActiveSheet.Unprotect
etc................

I have 72 sheets, and don't want to keep having to go in and paste in
more
lines everytime I add a new sheet.
Is there someway to tell it to repeat the Select & Unprotect lines x
number
of times, which would give a MUCH shorter code! Or, maybe there is a
way
to
tell it to keep repeating that until it gets to the last sheet in the
workbook ???

Phil

--
http://www.redbrick.dcu.ie/~pele

I changed the i = 5 to i = 12, presuming that is the sheet that will start
getting unprotected from which seems to work.

LASTLY (!!): How can I get this to loop from Sheet 12 to the end:
ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingRows:=True,
AllowFiltering:=True

--
http://www.redbrick.dcu.ie/~pele


"KL" wrote:

Try this:

For i=5 To Worksheets.Count
Worksheets(i).Unprotect
Next i

KL

"Phil Osman" wrote in message
...
That works, but is there a way to modify the code so that it only starts
from
SheetX and continues to the end of the workbook ?

--
http://www.redbrick.dcu.ie/~pele


"KL" wrote:

Hi Phil,

You can use loops like this one:

For each ws in ActiveWorkbook.Worksheets
ws.Unprotect
Next ws

Regards,
KL


"Phil Osman" wrote in message
...
I have a macro that simply looks like this:

Sheets("Template").Select
ActiveSheet.Unprotect
ActiveSheet.Next.Select
ActiveSheet.Unprotect
ActiveSheet.Next.Select
ActiveSheet.Unprotect
etc................

I have 72 sheets, and don't want to keep having to go in and paste in
more
lines everytime I add a new sheet.
Is there someway to tell it to repeat the Select & Unprotect lines x
number
of times, which would give a MUCH shorter code! Or, maybe there is a
way
to
tell it to keep repeating that until it gets to the last sheet in the
workbook ???

Phil

--
http://www.redbrick.dcu.ie/~pele