initiating a macro

does anybody know why this doesn't work:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Set rng = Worksheets("Sheet1").Range("A1")
i = 1

For Each sht In Worksheets
rng(i, 1) = sht.Name
i = i + 1
Next
End Sub

John,

It works fine for me,

Is Sheet1 definately called "Sheet1"? (i.e. the tab on the excel workbook
says "Sheet1".

What do you mean by doesn't work - it did take ages to run for me but that
may just be my laptop being funny.

Regards

Andi

"johnT" <[email protected]> wrote in message
news:[email protected]...
> does anybody know why this doesn't work:
>
> Private Sub Workbook_NewSheet(ByVal Sh As Object)
> Set rng = Worksheets("Sheet1").Range("A1")
> i = 1
>
> For Each sht In Worksheets
> rng(i, 1) = sht.Name
> i = i + 1
> Next
> End Sub
>

If you are trying to put the tab name into cell a1 on each sheet, use this
Sub shtname()
For Each sht In Worksheets
sht.Range("a1") = sht.Name
Next
End Sub

--
Don Guillett
SalesAid Software
[email protected]
"johnT" <[email protected]> wrote in message
news:[email protected]...
> does anybody know why this doesn't work:
>
> Private Sub Workbook_NewSheet(ByVal Sh As Object)
> Set rng = Worksheets("Sheet1").Range("A1")
> i = 1
>
> For Each sht In Worksheets
> rng(i, 1) = sht.Name
> i = i + 1
> Next
> End Sub
>

John,

It works fine for me,

Is Sheet1 definately called "Sheet1"? (i.e. the tab on the excel workbook
says "Sheet1".

What do you mean by doesn't work - it did take ages to run for me but that
may just be my laptop being funny.

Regards

Andi

"johnT" <[email protected]> wrote in message
news:[email protected]...
> does anybody know why this doesn't work:
>
> Private Sub Workbook_NewSheet(ByVal Sh As Object)
> Set rng = Worksheets("Sheet1").Range("A1")
> i = 1
>
> For Each sht In Worksheets
> rng(i, 1) = sht.Name
> i = i + 1
> Next
> End Sub
>

I would like to list all worksheet tab names on one sheet
such that when a new sheet is added, the list will
automatically update
>-----Original Message-----
>If you are trying to put the tab name into cell a1 on
each sheet, use this
>Sub shtname()
>For Each sht In Worksheets
>sht.Range("a1") = sht.Name
>Next
>End Sub
>
>--
>Don Guillett
>SalesAid Software
>[email protected]
>"johnT" <[email protected]> wrote in
message
>news:[email protected]...
>> does anybody know why this doesn't work:
>>
>> Private Sub Workbook_NewSheet(ByVal Sh As Object)
>> Set rng = Worksheets("Sheet1").Range("A1")
>> i = 1
>>
>> For Each sht In Worksheets
>> rng(i, 1) = sht.Name
>> i = i + 1
>> Next
>> End Sub
>>
>
>
>.
>

Ok, the macro seems to work if I insert a new sheet, but
is it possible to run the macro if I copy a sheet??

>-----Original Message-----
>John,
>
>It works fine for me,
>
>Is Sheet1 definately called "Sheet1"? (i.e. the tab on
the excel workbook
>says "Sheet1".
>
>What do you mean by doesn't work - it did take ages to
run for me but that
>may just be my laptop being funny.
>
>Regards
>
>Andi
>
>"johnT" <[email protected]> wrote in
message
>news:[email protected]...
>> does anybody know why this doesn't work:
>>
>> Private Sub Workbook_NewSheet(ByVal Sh As Object)
>> Set rng = Worksheets("Sheet1").Range("A1")
>> i = 1
>>
>> For Each sht In Worksheets
>> rng(i, 1) = sht.Name
>> i = i + 1
>> Next
>> End Sub
>>
>
>
>.
>

Not sure - one solution could be to do a count of the sheets when you open
the workbook and have a macro which counts if the number of sheets increases
then runs you code.

"johnT" <[email protected]> wrote in message
news:[email protected]...
> Ok, the macro seems to work if I insert a new sheet, but
> is it possible to run the macro if I copy a sheet??
>
> >-----Original Message-----
> >John,
> >
> >It works fine for me,
> >
> >Is Sheet1 definately called "Sheet1"? (i.e. the tab on
> the excel workbook
> >says "Sheet1".
> >
> >What do you mean by doesn't work - it did take ages to
> run for me but that
> >may just be my laptop being funny.
> >
> >Regards
> >
> >Andi
> >
> >"johnT" <[email protected]> wrote in
> message
> >news:[email protected]...
> >> does anybody know why this doesn't work:
> >>
> >> Private Sub Workbook_NewSheet(ByVal Sh As Object)
> >> Set rng = Worksheets("Sheet1").Range("A1")
> >> i = 1
> >>
> >> For Each sht In Worksheets
> >> rng(i, 1) = sht.Name
> >> i = i + 1
> >> Next
> >> End Sub
> >>
> >
> >
> >.
> >

do you have some sample code that i can copy??
>-----Original Message-----
>Not sure - one solution could be to do a count of the
sheets when you open
>the workbook and have a macro which counts if the number
of sheets increases
>then runs you code.
>
>"johnT" <[email protected]> wrote in
message
>news:[email protected]...
>> Ok, the macro seems to work if I insert a new sheet, but
>> is it possible to run the macro if I copy a sheet??
>>
>> >-----Original Message-----
>> >John,
>> >
>> >It works fine for me,
>> >
>> >Is Sheet1 definately called "Sheet1"? (i.e. the tab on
>> the excel workbook
>> >says "Sheet1".
>> >
>> >What do you mean by doesn't work - it did take ages to
>> run for me but that
>> >may just be my laptop being funny.
>> >
>> >Regards
>> >
>> >Andi
>> >
>> >"johnT" <[email protected]> wrote in
>> message
>> >news:[email protected]...
>> >> does anybody know why this doesn't work:
>> >>
>> >> Private Sub Workbook_NewSheet(ByVal Sh As Object)
>> >> Set rng = Worksheets("Sheet1").Range("A1")
>> >> i = 1
>> >>
>> >> For Each sht In Worksheets
>> >> rng(i, 1) = sht.Name
>> >> i = i + 1
>> >> Next
>> >> End Sub
>> >>
>> >
>> >
>> >.
>> >
>
>
>.
>

I misunderstood your original question but put this in the ThisWorkbook
module to run each time you open the workbook or you could run manually from
a button. No need to recount.

Private Sub Workbook_Open()
For i = 1 To Worksheets.Count
Sheets("sheet1").Cells(i, 1) = Sheets(i).Name
Next i
End Sub

--
Don Guillett
SalesAid Software
[email protected]
"johnT" <[email protected]> wrote in message
news:[email protected]...
> do you have some sample code that i can copy??
> >-----Original Message-----
> >Not sure - one solution could be to do a count of the
> sheets when you open
> >the workbook and have a macro which counts if the number
> of sheets increases
> >then runs you code.
> >
> >"johnT" <[email protected]> wrote in
> message
> >news:[email protected]...
> >> Ok, the macro seems to work if I insert a new sheet, but
> >> is it possible to run the macro if I copy a sheet??
> >>
> >> >-----Original Message-----
> >> >John,
> >> >
> >> >It works fine for me,
> >> >
> >> >Is Sheet1 definately called "Sheet1"? (i.e. the tab on
> >> the excel workbook
> >> >says "Sheet1".
> >> >
> >> >What do you mean by doesn't work - it did take ages to
> >> run for me but that
> >> >may just be my laptop being funny.
> >> >
> >> >Regards
> >> >
> >> >Andi
> >> >
> >> >"johnT" <[email protected]> wrote in
> >> message
> >> >news:[email protected]...
> >> >> does anybody know why this doesn't work:
> >> >>
> >> >> Private Sub Workbook_NewSheet(ByVal Sh As Object)
> >> >> Set rng = Worksheets("Sheet1").Range("A1")
> >> >> i = 1
> >> >>
> >> >> For Each sht In Worksheets
> >> >> rng(i, 1) = sht.Name
> >> >> i = i + 1
> >> >> Next
> >> >> End Sub
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Gentlemen,

I came up with something that works for me, based on all
your feedback, thanks for all your help.

>-----Original Message-----
>I misunderstood your original question but put this in
the ThisWorkbook
>module to run each time you open the workbook or you
could run manually from
>a button. No need to recount.
>
>Private Sub Workbook_Open()
>For i = 1 To Worksheets.Count
>Sheets("sheet1").Cells(i, 1) = Sheets(i).Name
>Next i
>End Sub
>
>--
>Don Guillett
>SalesAid Software
>[email protected]
>"johnT" <[email protected]> wrote in
message
>news:[email protected]...
>> do you have some sample code that i can copy??
>> >-----Original Message-----
>> >Not sure - one solution could be to do a count of the
>> sheets when you open
>> >the workbook and have a macro which counts if the
number
>> of sheets increases
>> >then runs you code.
>> >
>> >"johnT" <[email protected]> wrote in
>> message
>> >news:[email protected]...
>> >> Ok, the macro seems to work if I insert a new sheet,
but
>> >> is it possible to run the macro if I copy a sheet??
>> >>
>> >> >-----Original Message-----
>> >> >John,
>> >> >
>> >> >It works fine for me,
>> >> >
>> >> >Is Sheet1 definately called "Sheet1"? (i.e. the tab
on
>> >> the excel workbook
>> >> >says "Sheet1".
>> >> >
>> >> >What do you mean by doesn't work - it did take ages
to
>> >> run for me but that
>> >> >may just be my laptop being funny.
>> >> >
>> >> >Regards
>> >> >
>> >> >Andi
>> >> >
>> >> >"johnT" <[email protected]> wrote
in
>> >> message
>> >> >news:[email protected]...
>> >> >> does anybody know why this doesn't work:
>> >> >>
>> >> >> Private Sub Workbook_NewSheet(ByVal Sh As Object)
>> >> >> Set rng = Worksheets("Sheet1").Range("A1")
>> >> >> i = 1
>> >> >>
>> >> >> For Each sht In Worksheets
>> >> >> rng(i, 1) = sht.Name
>> >> >> i = i + 1
>> >> >> Next
>> >> >> End Sub
>> >> >>
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>
>.
>