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