VBA to insert list of sheet names

  • Thread starter Thread starter Katherine Vale
  • Start date Start date
K

Katherine Vale

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 :)
 
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
 
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 :)
 
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 (e-mail address removed)
 
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 (e-mail address removed)
 
Back
Top