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" wrote in message
...
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

"johnT" wrote in message
...
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" wrote in message
...
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

"johnT" wrote in
message
...
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" wrote in
message
...
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" wrote in message
...
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" wrote in
message
...
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" wrote in
message
...
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" wrote in
message
...
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

"johnT" wrote in message
...
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" wrote in
message
...
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" wrote in
message
...
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

"johnT" wrote in
message
...
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" wrote in
message
...
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" wrote
in
message
...
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



.



.



.