If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Numbering worksheets
I am using multiple worksheets within a workbook. In
cell A1 of each sheet is the number representing the position of the worksheet (i.e. the first worksheet has '1.' in cell A1, the second worksheet has '2.' in cell A1). Is there a way for Excel to automatically fill in this number? I have used [='sheet1'A1+1] however if I add a sheet between two existing sheets then the formula is broken (i.e. adding a new worksheet between the existing 15th and 16th worksheets, the 16th sheet becomes the 17th but cell A1 will still point to the 15th sheet.) |
#2
|
|||
|
|||
Numbering worksheets
Does this help?
Private Sub Workbook_NewSheet(ByVal Sh As Object) For i = 1 To Sheets.Count Sheets(i).Range("A1") = i Next End Sub Geoff "Shelly" wrote in message ... I am using multiple worksheets within a workbook. In cell A1 of each sheet is the number representing the position of the worksheet (i.e. the first worksheet has '1.' in cell A1, the second worksheet has '2.' in cell A1). Is there a way for Excel to automatically fill in this number? I have used [='sheet1'A1+1] however if I add a sheet between two existing sheets then the formula is broken (i.e. adding a new worksheet between the existing 15th and 16th worksheets, the 16th sheet becomes the 17th but cell A1 will still point to the 15th sheet.) |
#3
|
|||
|
|||
Numbering worksheets
Does this help?
Private Sub Workbook_NewSheet(ByVal Sh As Object) For i = 1 To Sheets.Count Sheets(i).Range("A1") = i Next End Sub Geoff "Shelly" wrote in message ... I am using multiple worksheets within a workbook. In cell A1 of each sheet is the number representing the position of the worksheet (i.e. the first worksheet has '1.' in cell A1, the second worksheet has '2.' in cell A1). Is there a way for Excel to automatically fill in this number? I have used [='sheet1'A1+1] however if I add a sheet between two existing sheets then the formula is broken (i.e. adding a new worksheet between the existing 15th and 16th worksheets, the 16th sheet becomes the 17th but cell A1 will still point to the 15th sheet.) |
#4
|
|||
|
|||
Numbering worksheets
Does this help?
Private Sub Workbook_NewSheet(ByVal Sh As Object) For i = 1 To Sheets.Count Sheets(i).Range("A1") = i Next End Sub Geoff NB: I already posted this reply on 3 October, but it did not appear in my news-feed, so I am not sure what happened. Apologies to anyone who has received this post more than once. "Shelly" wrote in message ... I am using multiple worksheets within a workbook. In cell A1 of each sheet is the number representing the position of the worksheet (i.e. the first worksheet has '1.' in cell A1, the second worksheet has '2.' in cell A1). Is there a way for Excel to automatically fill in this number? I have used [='sheet1'A1+1] however if I add a sheet between two existing sheets then the formula is broken (i.e. adding a new worksheet between the existing 15th and 16th worksheets, the 16th sheet becomes the 17th but cell A1 will still point to the 15th sheet.) |
Thread Tools | |
Display Modes | |
|
|