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.
--
Dennis Cheung

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" wrote:

> 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.
> --
> 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.
--
Dennis Cheung


"Sam Wilson" wrote:

> 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" wrote:
>
> > 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.
> > --
> > Dennis Cheung

Yes, several ways...

Change

For i = 0 To 99

to

For i = 99 To 0 step -1

being one.

Sam

"Dennis Cheung" wrote:

> 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.
> --
> Dennis Cheung
>
>
> "Sam Wilson" wrote:
>
> > 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" wrote:
> >
> > > 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.
> > > --
> > > Dennis Cheung

dear sam,

it works the same as before. any other way?
--
Dennis Cheung


"Sam Wilson" wrote:

> Yes, several ways...
>
> Change
>
> For i = 0 To 99
>
> to
>
> For i = 99 To 0 step -1
>
> being one.
>
> Sam
>
> "Dennis Cheung" wrote:
>
> > 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.
> > --
> > Dennis Cheung
> >
> >
> > "Sam Wilson" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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.
> > > > --
> > > > Dennis Cheung