Thread
:
VBA to insert list of sheet names
View Single Post
#
4
Posted to microsoft.public.excel.programming
Chip Pearson
external usenet poster
Posts: 7,247
VBA to insert list of sheet names
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 With Quote
Chip Pearson
View Public Profile
Find all posts by Chip Pearson