Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Name
on sheet1 in cell A1 I want for the name of sheet1 to
appear in this cell. In cell A2 I want for the name of sheet2 to appear. In cell A3 I want for the name of sheet3 to appear and so on until sheet50..... This means A1:A50 will be a list of sheet names. Also lets say if there are only 25 sheets instead of 50. If this is the case I need for the code to leave the remaining 25 cells in columnA blank and not put in a sheet name because there are no sheets. So I need the code to list the names of all the sheets but if there are no sheets to leave the cell blank. Thank you Todd Huttenstine |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Name
Todd,
This routine -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... on sheet1 in cell A1 I want for the name of sheet1 to appear in this cell. In cell A2 I want for the name of sheet2 to appear. In cell A3 I want for the name of sheet3 to appear and so on until sheet50..... This means A1:A50 will be a list of sheet names. Also lets say if there are only 25 sheets instead of 50. If this is the case I need for the code to leave the remaining 25 cells in columnA blank and not put in a sheet name because there are no sheets. So I need the code to list the names of all the sheets but if there are no sheets to leave the cell blank. Thank you Todd Huttenstine |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Name
Todd
Sub SheetNames() Dim i As Long For i = 1 To Worksheets.Count Range("A" & i) = Sheets(i).Name Next 'i End Sub Regards Trevor "Todd Huttenstine" wrote in message ... on sheet1 in cell A1 I want for the name of sheet1 to appear in this cell. In cell A2 I want for the name of sheet2 to appear. In cell A3 I want for the name of sheet3 to appear and so on until sheet50..... This means A1:A50 will be a list of sheet names. Also lets say if there are only 25 sheets instead of 50. If this is the case I need for the code to leave the remaining 25 cells in columnA blank and not put in a sheet name because there are no sheets. So I need the code to list the names of all the sheets but if there are no sheets to leave the cell blank. Thank you Todd Huttenstine |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Name
Todd,
Sorry again, the touchpad is playing up tonight. Here's the code. Sub SheetNames() Dim sh As Worksheet Dim i As Long Columns(1).ClearContents i = 1 For Each sh In ThisWorkbook.Worksheets ActiveSheet.Cells(i, "A").Value = sh.Name i = i + 1 Next sh End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... on sheet1 in cell A1 I want for the name of sheet1 to appear in this cell. In cell A2 I want for the name of sheet2 to appear. In cell A3 I want for the name of sheet3 to appear and so on until sheet50..... This means A1:A50 will be a list of sheet names. Also lets say if there are only 25 sheets instead of 50. If this is the case I need for the code to leave the remaining 25 cells in columnA blank and not put in a sheet name because there are no sheets. So I need the code to list the names of all the sheets but if there are no sheets to leave the cell blank. Thank you Todd Huttenstine |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Name
Thank you both
-----Original Message----- on sheet1 in cell A1 I want for the name of sheet1 to appear in this cell. In cell A2 I want for the name of sheet2 to appear. In cell A3 I want for the name of sheet3 to appear and so on until sheet50..... This means A1:A50 will be a list of sheet names. Also lets say if there are only 25 sheets instead of 50. If this is the case I need for the code to leave the remaining 25 cells in columnA blank and not put in a sheet name because there are no sheets. So I need the code to list the names of all the sheets but if there are no sheets to leave the cell blank. Thank you Todd Huttenstine . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Name
Sorry for this but I have to make a modification to this
code. Actually I would like for it to start listing the names of the work sheets in Cell A3 instead of A1. And also I need for it to start listing the names of the worksheets on sheet3 instead of the first sheet in the workbook. Thank you. -----Original Message----- Todd, Sorry again, the touchpad is playing up tonight. Here's the code. Sub SheetNames() Dim sh As Worksheet Dim i As Long Columns(1).ClearContents i = 1 For Each sh In ThisWorkbook.Worksheets ActiveSheet.Cells(i, "A").Value = sh.Name i = i + 1 Next sh End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... on sheet1 in cell A1 I want for the name of sheet1 to appear in this cell. In cell A2 I want for the name of sheet2 to appear. In cell A3 I want for the name of sheet3 to appear and so on until sheet50..... This means A1:A50 will be a list of sheet names. Also lets say if there are only 25 sheets instead of 50. If this is the case I need for the code to leave the remaining 25 cells in columnA blank and not put in a sheet name because there are no sheets. So I need the code to list the names of all the sheets but if there are no sheets to leave the cell blank. Thank you Todd Huttenstine . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Name
Todd,
you are so demanding<vbg. Hope this does it Sub SheetNames() Dim sh As Worksheet Dim i As Long Columns(1).ClearContents i = 3 For Each sh In ThisWorkbook.Worksheets Worksheets("Sheet3").Cells(i, "A").Value = sh.Name i = i + 1 Next sh End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... Sorry for this but I have to make a modification to this code. Actually I would like for it to start listing the names of the work sheets in Cell A3 instead of A1. And also I need for it to start listing the names of the worksheets on sheet3 instead of the first sheet in the workbook. Thank you. -----Original Message----- Todd, Sorry again, the touchpad is playing up tonight. Here's the code. Sub SheetNames() Dim sh As Worksheet Dim i As Long Columns(1).ClearContents i = 1 For Each sh In ThisWorkbook.Worksheets ActiveSheet.Cells(i, "A").Value = sh.Name i = i + 1 Next sh End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... on sheet1 in cell A1 I want for the name of sheet1 to appear in this cell. In cell A2 I want for the name of sheet2 to appear. In cell A3 I want for the name of sheet3 to appear and so on until sheet50..... This means A1:A50 will be a list of sheet names. Also lets say if there are only 25 sheets instead of 50. If this is the case I need for the code to leave the remaining 25 cells in columnA blank and not put in a sheet name because there are no sheets. So I need the code to list the names of all the sheets but if there are no sheets to leave the cell blank. Thank you Todd Huttenstine . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Name
I think Bob meant something like:
Sub SheetNames() Dim sh As Worksheet Dim i As Long with Worksheets("Sheet3") .Columns(1).ClearContents i = 3 For Each sh In ThisWorkbook.Worksheets .Cells(i, "A").Value = sh.Name i = i + 1 Next sh End with End Sub Otherwise it would clear columns(1) on the activesheet. -- Regards, Tom Ogilvy Bob Phillips wrote in message ... Todd, you are so demanding<vbg. Hope this does it Sub SheetNames() Dim sh As Worksheet Dim i As Long Columns(1).ClearContents i = 3 For Each sh In ThisWorkbook.Worksheets Worksheets("Sheet3").Cells(i, "A").Value = sh.Name i = i + 1 Next sh End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... Sorry for this but I have to make a modification to this code. Actually I would like for it to start listing the names of the work sheets in Cell A3 instead of A1. And also I need for it to start listing the names of the worksheets on sheet3 instead of the first sheet in the workbook. Thank you. -----Original Message----- Todd, Sorry again, the touchpad is playing up tonight. Here's the code. Sub SheetNames() Dim sh As Worksheet Dim i As Long Columns(1).ClearContents i = 1 For Each sh In ThisWorkbook.Worksheets ActiveSheet.Cells(i, "A").Value = sh.Name i = i + 1 Next sh End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... on sheet1 in cell A1 I want for the name of sheet1 to appear in this cell. In cell A2 I want for the name of sheet2 to appear. In cell A3 I want for the name of sheet3 to appear and so on until sheet50..... This means A1:A50 will be a list of sheet names. Also lets say if there are only 25 sheets instead of 50. If this is the case I need for the code to leave the remaining 25 cells in columnA blank and not put in a sheet name because there are no sheets. So I need the code to list the names of all the sheets but if there are no sheets to leave the cell blank. Thank you Todd Huttenstine . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Name
Its almost done. Currently, the code lists the names of
all worksheets, but instead, I need it to start listing the names of all worksheets starting with the name of sheet3. Thanx -----Original Message----- Todd, you are so demanding<vbg. Hope this does it Sub SheetNames() Dim sh As Worksheet Dim i As Long Columns(1).ClearContents i = 3 For Each sh In ThisWorkbook.Worksheets Worksheets("Sheet3").Cells(i, "A").Value = sh.Name i = i + 1 Next sh End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... Sorry for this but I have to make a modification to this code. Actually I would like for it to start listing the names of the work sheets in Cell A3 instead of A1. And also I need for it to start listing the names of the worksheets on sheet3 instead of the first sheet in the workbook. Thank you. -----Original Message----- Todd, Sorry again, the touchpad is playing up tonight. Here's the code. Sub SheetNames() Dim sh As Worksheet Dim i As Long Columns(1).ClearContents i = 1 For Each sh In ThisWorkbook.Worksheets ActiveSheet.Cells(i, "A").Value = sh.Name i = i + 1 Next sh End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... on sheet1 in cell A1 I want for the name of sheet1 to appear in this cell. In cell A2 I want for the name of sheet2 to appear. In cell A3 I want for the name of sheet3 to appear and so on until sheet50..... This means A1:A50 will be a list of sheet names. Also lets say if there are only 25 sheets instead of 50. If this is the case I need for the code to leave the remaining 25 cells in columnA blank and not put in a sheet name because there are no sheets. So I need the code to list the names of all the sheets but if there are no sheets to leave the cell blank. Thank you Todd Huttenstine . . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Name
Sub SheetNames()
Dim sh As Worksheet Dim i As Long Dim bFlag as Boolean with Worksheets("Sheet3") .Columns(1).ClearContents i = 3 For Each sh In ThisWorkbook.Worksheets if lcase(sh.name) = "sheet3" then bFlag = True if bFlag then .Cells(i, "A").Value = sh.Name i = i + 1 end if Next sh End with End Sub -- Regards, Tom Ogilvy Tom Ogilvy wrote in message ... I think Bob meant something like: Sub SheetNames() Dim sh As Worksheet Dim i As Long with Worksheets("Sheet3") .Columns(1).ClearContents i = 3 For Each sh In ThisWorkbook.Worksheets .Cells(i, "A").Value = sh.Name i = i + 1 Next sh End with End Sub Otherwise it would clear columns(1) on the activesheet. -- Regards, Tom Ogilvy Bob Phillips wrote in message ... Todd, you are so demanding<vbg. Hope this does it Sub SheetNames() Dim sh As Worksheet Dim i As Long Columns(1).ClearContents i = 3 For Each sh In ThisWorkbook.Worksheets Worksheets("Sheet3").Cells(i, "A").Value = sh.Name i = i + 1 Next sh End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... Sorry for this but I have to make a modification to this code. Actually I would like for it to start listing the names of the work sheets in Cell A3 instead of A1. And also I need for it to start listing the names of the worksheets on sheet3 instead of the first sheet in the workbook. Thank you. -----Original Message----- Todd, Sorry again, the touchpad is playing up tonight. Here's the code. Sub SheetNames() Dim sh As Worksheet Dim i As Long Columns(1).ClearContents i = 1 For Each sh In ThisWorkbook.Worksheets ActiveSheet.Cells(i, "A").Value = sh.Name i = i + 1 Next sh End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... on sheet1 in cell A1 I want for the name of sheet1 to appear in this cell. In cell A2 I want for the name of sheet2 to appear. In cell A3 I want for the name of sheet3 to appear and so on until sheet50..... This means A1:A50 will be a list of sheet names. Also lets say if there are only 25 sheets instead of 50. If this is the case I need for the code to leave the remaining 25 cells in columnA blank and not put in a sheet name because there are no sheets. So I need the code to list the names of all the sheets but if there are no sheets to leave the cell blank. Thank you Todd Huttenstine . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Name
I have modified the code (see below)
Here is the problem. According to the current code, the first sheet displayed is "template"(because it is written in the code), however I need it to start with the next sheet which is unknown because the name of that sheet may change. That sheet will always be sheet number 4 so is there anyway to specify to start with sheet number 4? For instance. The next sheet after sheet "template" is called "Tammy". The code needs to specify to start listing the sheets with sheet4 (which in this case is "Tammy") Therefor I need to specify in the code to start listing sheets with sheet4, instead of the name of the actual sheet. Dim sh As Worksheet Dim i As Long Dim bFlag As Boolean With Worksheets(2) .Columns(10).ClearContents i = 3 For Each sh In ThisWorkbook.Worksheets If LCase(sh.name) = "template" Then bFlag = True If bFlag Then .Cells(i, "J").Value = sh.name i = i + 1 End If Next sh End With |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Name
Sub SheetNames()
Dim sh As Worksheet Dim i As Long with Worksheets("Sheet3") .Columns(1).ClearContents j = 0 i = 3 For Each sh In ThisWorkbook.Worksheets j = j + 1 if j = 4 then .Cells(i, "A").Value = sh.Name i = i + 1 end if Next sh End with End Sub or Sub SheetNames() Dim sh As Worksheet Dim i As Long Dim bFlag as Boolean with Worksheets("Sheet3") .Columns(1).ClearContents i = 3 For Each sh In ThisWorkbook.Worksheets if bFlag then .Cells(i, "A").Value = sh.Name i = i + 1 end if if lcase(sh.name) = "template" then bFlag = True Next sh End with End Sub -- Regards, Tom Ogilvy Todd Huttenstine wrote in message ... I have modified the code (see below) Here is the problem. According to the current code, the first sheet displayed is "template"(because it is written in the code), however I need it to start with the next sheet which is unknown because the name of that sheet may change. That sheet will always be sheet number 4 so is there anyway to specify to start with sheet number 4? For instance. The next sheet after sheet "template" is called "Tammy". The code needs to specify to start listing the sheets with sheet4 (which in this case is "Tammy") Therefor I need to specify in the code to start listing sheets with sheet4, instead of the name of the actual sheet. Dim sh As Worksheet Dim i As Long Dim bFlag As Boolean With Worksheets(2) .Columns(10).ClearContents i = 3 For Each sh In ThisWorkbook.Worksheets If LCase(sh.name) = "template" Then bFlag = True If bFlag Then .Cells(i, "J").Value = sh.name i = i + 1 End If Next sh End With |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Name
Missed your code
With Worksheets(2) .Columns(10).ClearContents i = 3 For Each sh In ThisWorkbook.Worksheets If bFlag Then .Cells(i, "J").Value = sh.name i = i + 1 End If If LCase(sh.name) = "template" Then bFlag = True Next sh End With To start adding names after sheet template in the tab order -- Regards, Tom Ogilvy Todd Huttenstine wrote in message ... I have modified the code (see below) Here is the problem. According to the current code, the first sheet displayed is "template"(because it is written in the code), however I need it to start with the next sheet which is unknown because the name of that sheet may change. That sheet will always be sheet number 4 so is there anyway to specify to start with sheet number 4? For instance. The next sheet after sheet "template" is called "Tammy". The code needs to specify to start listing the sheets with sheet4 (which in this case is "Tammy") Therefor I need to specify in the code to start listing sheets with sheet4, instead of the name of the actual sheet. Dim sh As Worksheet Dim i As Long Dim bFlag As Boolean With Worksheets(2) .Columns(10).ClearContents i = 3 For Each sh In ThisWorkbook.Worksheets If LCase(sh.name) = "template" Then bFlag = True If bFlag Then .Cells(i, "J").Value = sh.name i = i + 1 End If Next sh End With |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Name
That got it! Thanks again.
-----Original Message----- Sub SheetNames() Dim sh As Worksheet Dim i As Long with Worksheets("Sheet3") .Columns(1).ClearContents j = 0 i = 3 For Each sh In ThisWorkbook.Worksheets j = j + 1 if j = 4 then .Cells(i, "A").Value = sh.Name i = i + 1 end if Next sh End with End Sub or Sub SheetNames() Dim sh As Worksheet Dim i As Long Dim bFlag as Boolean with Worksheets("Sheet3") .Columns(1).ClearContents i = 3 For Each sh In ThisWorkbook.Worksheets if bFlag then .Cells(i, "A").Value = sh.Name i = i + 1 end if if lcase(sh.name) = "template" then bFlag = True Next sh End with End Sub -- Regards, Tom Ogilvy Todd Huttenstine wrote in message ... I have modified the code (see below) Here is the problem. According to the current code, the first sheet displayed is "template"(because it is written in the code), however I need it to start with the next sheet which is unknown because the name of that sheet may change. That sheet will always be sheet number 4 so is there anyway to specify to start with sheet number 4? For instance. The next sheet after sheet "template" is called "Tammy". The code needs to specify to start listing the sheets with sheet4 (which in this case is "Tammy") Therefor I need to specify in the code to start listing sheets with sheet4, instead of the name of the actual sheet. Dim sh As Worksheet Dim i As Long Dim bFlag As Boolean With Worksheets(2) .Columns(10).ClearContents i = 3 For Each sh In ThisWorkbook.Worksheets If LCase(sh.name) = "template" Then bFlag = True If bFlag Then .Cells(i, "J").Value = sh.name i = i + 1 End If Next sh End With . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatically appending newly added data on worksheet to a master list worksheet | Links and Linking in Excel | |||
plot graph from multiple worksheet as embedded chart object on every worksheet | New Users to Excel | |||
Upload multiple text files into 1 excel worksheet + put the filename as the first column in the worksheet | Excel Worksheet Functions | |||
I want in one worksheet to relatively link to/reference cells in another without changing the format of the current worksheet. | Excel Discussion (Misc queries) | |||
Attaching a JET database to an Excel Worksheet OR storing large binary data in a worksheet | Excel Programming |