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 :)

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 :)

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 :)




.

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 :)




.

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 :)




.