Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to insert list of sheet names
I've got the following piece of code to create a list of
all the sheet names in a workbook. I want to modify this so that the list of names created is automatically inserted into Sheet1 (for example) starting at cell A5, rather than creating a new sheet with the names on as is happening at the moment. Private Sub CommandButton1_Click() Set NewSheet = Sheets.Add(Type:=xlWorksheet) For i = 1 To Sheets.Count NewSheet.Cells(i, 1).Value = Sheets(i).Name Next i End Sub Any suggestions greatly appreciated :) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to insert list of sheet names
Katherine,
Try Private Sub CommandButton1_Click() For i = 1 To Sheets.Count Sheets("Sheet1").Range("A5")(i,1).Value = Sheets(i).Name Next i End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com "Katherine Vale" wrote in message ... I've got the following piece of code to create a list of all the sheet names in a workbook. I want to modify this so that the list of names created is automatically inserted into Sheet1 (for example) starting at cell A5, rather than creating a new sheet with the names on as is happening at the moment. Private Sub CommandButton1_Click() Set NewSheet = Sheets.Add(Type:=xlWorksheet) For i = 1 To Sheets.Count NewSheet.Cells(i, 1).Value = Sheets(i).Name Next i End Sub Any suggestions greatly appreciated :) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to insert list of sheet names
Fantastic, thanks Chip. Works like a charm, but i forgot
that i had some sheets in the workbook hidden - I dont want to see the names of the hidden files in the list, how can i do this? Thanks for your help :) -----Original Message----- Katherine, Try Private Sub CommandButton1_Click() For i = 1 To Sheets.Count Sheets("Sheet1").Range("A5")(i,1).Value = Sheets (i).Name Next i End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com "Katherine Vale" wrote in message ... I've got the following piece of code to create a list of all the sheet names in a workbook. I want to modify this so that the list of names created is automatically inserted into Sheet1 (for example) starting at cell A5, rather than creating a new sheet with the names on as is happening at the moment. Private Sub CommandButton1_Click() Set NewSheet = Sheets.Add(Type:=xlWorksheet) For i = 1 To Sheets.Count NewSheet.Cells(i, 1).Value = Sheets(i).Name Next i End Sub Any suggestions greatly appreciated :) . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to insert list of sheet names
The line of code
Set Rng = Rng(2,1) moves Rng down one row. It is essentially the same thing as Set Rng = Rng.Offset(1,0) but uses a 1-based offset rather than a 0-based offset. It is just another method of addressing one range from another range. See http://www.cpearson.com/excel/cells.htm for more details. -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com "JMay" wrote in message news:WJL4b.42677$xf.33423@lakeread06... Chip, thanks for code.. What does this line do?: Set Rng = Rng(2, 1) TIA, "Chip Pearson" wrote in message ... Katherine, Try something like the following: Private Sub CommandButton1_Click() Dim WS As Worksheet Dim Rng As Range Set Rng = Worksheets("Sheet1").Range("A5") For Each WS In Sheets If WS.Visible = xlSheetVisible Then Rng.Value = WS.Name Set Rng = Rng(2, 1) End If Next WS End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com "katherine" wrote in message ... Fantastic, thanks Chip. Works like a charm, but i forgot that i had some sheets in the workbook hidden - I dont want to see the names of the hidden files in the list, how can i do this? Thanks for your help :) -----Original Message----- Katherine, Try Private Sub CommandButton1_Click() For i = 1 To Sheets.Count Sheets("Sheet1").Range("A5")(i,1).Value = Sheets (i).Name Next i End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com "Katherine Vale" wrote in message ... I've got the following piece of code to create a list of all the sheet names in a workbook. I want to modify this so that the list of names created is automatically inserted into Sheet1 (for example) starting at cell A5, rather than creating a new sheet with the names on as is happening at the moment. Private Sub CommandButton1_Click() Set NewSheet = Sheets.Add(Type:=xlWorksheet) For i = 1 To Sheets.Count NewSheet.Cells(i, 1).Value = Sheets(i).Name Next i End Sub Any suggestions greatly appreciated :) . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
list sheet names vertically below the active cell - need macro. | Excel Worksheet Functions | |||
Insert Incremental sheet names in a workbook | Excel Worksheet Functions | |||
how do i set up a list of names on a sheet frm various sheets in e | Excel Discussion (Misc queries) | |||
How do I display list of tab names used in a workbook on a sheet | Excel Worksheet Functions | |||
Function to List an Excel Workbook's Sheet Names | Excel Discussion (Misc queries) |