creat sheets with assigned name and protected

[Dennis Cheung]

dear masters,

in sheet 1, cell a1 to a100 contents are
a1 abc
a2 bcd
a3 cde
a4 to a100 are blank
i need a marco to creat sheets with names according to contents in a1 to
a100. no sheet will be created if the cell is empty.
then those newly created sheets are protect by password "123456".
can i do that?

thanks in advance.

 

[Sam Wilson]

Sub test()

Dim i As Integer
Dim xws As Worksheet
Dim ws As Worksheet

Set xws = ActiveSheet

For i = 0 To 99
If Not IsEmpty(xws.Range("A1").Offset(i, 0)) Then
Set ws = Worksheets.Add
ws.Name = xws.Range("A1").Offset(i, 0).Value
ws.Protect "123456"
End If
Next i

End Sub

 

[Dennis Cheung]

Dear Sam,

Thanks for your help, it works good.

can i make those new sheets are created after sheet 1, and they are in order
from a1 to a100. the code you send me created sheets in order of backward.

 

[Sam Wilson]

Yes, several ways...

Change

For i = 0 To 99

to

For i = 99 To 0 step -1

being one.

Sam

 

[Dennis Cheung]

dear sam,

it works the same as before. any other way?