creat sheets with assigned name and protected

  • Thread starter Thread starter Dennis Cheung
  • Start date Start date
D

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.
 
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
 
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.
 
Yes, several ways...

Change

For i = 0 To 99

to

For i = 99 To 0 step -1

being one.

Sam
 
Back
Top