Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 . |
#6
|
|||
|
|||
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 . |
#7
|
|||
|
|||
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 . |
#8
|
|||
|
|||
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 . . |
#10
|
|||
|
|||
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 . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
automatic macro update | Excel Worksheet Functions | |||
Date macro | Excel Discussion (Misc queries) | |||
Can't get simple macro to run | Excel Worksheet Functions | |||
Macro and If Statement | Excel Discussion (Misc queries) |