Dear Helpers,

I need to extract all the worksheet names in a summary worksheet. I copied
the following VBA from the Office Discussion Groups Q & A

Quote

Sub SheetList()
For Each ws In Worksheets
i = i + 1
Cells(i, 1) = ws.Name
Next
End Sub €“ by Mr Raypayette

Unquoted

The above vba works well but always have the result located at A1. Is it
possible to put it to B3 or any designated cell I choose.

Thanks in advance

Fanny


"raypayette" wrote:

Click to show or hide original message or reply text.



You would have to use VBE:
Sub SheetList()
For Each ws In Worksheets
i = i + 1
Cells(i, 1) = ws.Name
Next
End Sub


--
raypayette


------------------------------------------------------------------------
raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
View this thread: http://www.excelforum.com/showthread...hreadid=566968

Sub SheetList()
i = 0
Set r = Range(Application.InputBox(prompt:="enter start cell:", Type:=2))
For Each ws In Worksheets
r.Offset(i, 0) = ws.Name
i = i + 1
Next
End Sub

run the sub and supply the address
--
Gary''s Student - gsnu200746


"Fanny" wrote:

Dear Helpers,

I need to extract all the worksheet names in a summary worksheet. I copied
the following VBA from the Office Discussion Groups Q & A

Quote

Sub SheetList()
For Each ws In Worksheets
i = i + 1
Cells(i, 1) = ws.Name
Next
End Sub €“ by Mr Raypayette

Unquoted

The above vba works well but always have the result located at A1. Is it
possible to put it to B3 or any designated cell I choose.

Thanks in advance

Fanny


"raypayette" wrote:

Click to show or hide original message or reply text.



You would have to use VBE:
Sub SheetList()
For Each ws In Worksheets
i = i + 1
Cells(i, 1) = ws.Name
Next
End Sub


--
raypayette


------------------------------------------------------------------------
raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
View this thread: http://www.excelforum.com/showthread...hreadid=566968

Another version:

Option Explicit
Sub SheetList()

Dim i As Long
Dim DestCell As Range

Set DestCell = Nothing
On Error Resume Next
Set DestCell = Application.InputBox(PRompt:="Pick a cell", Type:=8)
On Error GoTo 0

If DestCell Is Nothing Then
'do nothing, user hit cancel
Else
For i = 1 To Worksheets.Count
DestCell.Value = "'" & Worksheets(i).Name
Set DestCell = DestCell.Offset(1, 0)
Next i
End If

End Sub



Fanny wrote:

Dear Helpers,

I need to extract all the worksheet names in a summary worksheet. I copied
the following VBA from the Office Discussion Groups Q & A

Quote

Sub SheetList()
For Each ws In Worksheets
i = i + 1
Cells(i, 1) = ws.Name
Next
End Sub €“ by Mr Raypayette

Unquoted

The above vba works well but always have the result located at A1. Is it
possible to put it to B3 or any designated cell I choose.

Thanks in advance

Fanny

"raypayette" wrote:

Click to show or hide original message or reply text.


You would have to use VBE:
Sub SheetList()
For Each ws In Worksheets
i = i + 1
Cells(i, 1) = ws.Name
Next
End Sub


--
raypayette


------------------------------------------------------------------------
raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
View this thread: http://www.excelforum.com/showthread...hreadid=566968



--

Dave Peterson

I bet you meant type:=8.

Gary''s Student wrote:

Sub SheetList()
i = 0
Set r = Range(Application.InputBox(prompt:="enter start cell:", Type:=2))
For Each ws In Worksheets
r.Offset(i, 0) = ws.Name
i = i + 1
Next
End Sub

run the sub and supply the address
--
Gary''s Student - gsnu200746

"Fanny" wrote:

Dear Helpers,

I need to extract all the worksheet names in a summary worksheet. I copied
the following VBA from the Office Discussion Groups Q & A

Quote

Sub SheetList()
For Each ws In Worksheets
i = i + 1
Cells(i, 1) = ws.Name
Next
End Sub €“ by Mr Raypayette

Unquoted

The above vba works well but always have the result located at A1. Is it
possible to put it to B3 or any designated cell I choose.

Thanks in advance

Fanny


"raypayette" wrote:

Click to show or hide original message or reply text.



You would have to use VBE:
Sub SheetList()
For Each ws In Worksheets
i = i + 1
Cells(i, 1) = ws.Name
Next
End Sub


--
raypayette


------------------------------------------------------------------------
raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
View this thread: http://www.excelforum.com/showthread...hreadid=566968




--

Dave Peterson

Hi Dave:

By habit, I usually get addresses as a string and then convert it to a
range. It's a habit I should break.
--
Gary''s Student - gsnu200746


"Dave Peterson" wrote:

I bet you meant type:=8.

Gary''s Student wrote:

Sub SheetList()
i = 0
Set r = Range(Application.InputBox(prompt:="enter start cell:", Type:=2))
For Each ws In Worksheets
r.Offset(i, 0) = ws.Name
i = i + 1
Next
End Sub

run the sub and supply the address
--
Gary''s Student - gsnu200746

"Fanny" wrote:

Dear Helpers,

I need to extract all the worksheet names in a summary worksheet. I copied
the following VBA from the Office Discussion Groups Q & A

Quote

Sub SheetList()
For Each ws In Worksheets
i = i + 1
Cells(i, 1) = ws.Name
Next
End Sub €€œ by Mr Raypayette

Unquoted

The above vba works well but always have the result located at A1. Is it
possible to put it to B3 or any designated cell I choose.

Thanks in advance

Fanny


"raypayette" wrote:

Click to show or hide original message or reply text.



You would have to use VBE:
Sub SheetList()
For Each ws In Worksheets
i = i + 1
Cells(i, 1) = ws.Name
Next
End Sub


--
raypayette


------------------------------------------------------------------------
raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
View this thread: http://www.excelforum.com/showthread...hreadid=566968




--

Dave Peterson

Dear Mr Dave Peterson,

Your solution worsk perfect. Thank you a lot.

Fanny

"Dave Peterson" wrote:

Another version:

Option Explicit
Sub SheetList()

Dim i As Long
Dim DestCell As Range

Set DestCell = Nothing
On Error Resume Next
Set DestCell = Application.InputBox(PRompt:="Pick a cell", Type:=8)
On Error GoTo 0

If DestCell Is Nothing Then
'do nothing, user hit cancel
Else
For i = 1 To Worksheets.Count
DestCell.Value = "'" & Worksheets(i).Name
Set DestCell = DestCell.Offset(1, 0)
Next i
End If

End Sub



Fanny wrote:

Dear Helpers,

I need to extract all the worksheet names in a summary worksheet. I copied
the following VBA from the Office Discussion Groups Q & A

Quote

Sub SheetList()
For Each ws In Worksheets
i = i + 1
Cells(i, 1) = ws.Name
Next
End Sub €€œ by Mr Raypayette

Unquoted

The above vba works well but always have the result located at A1. Is it
possible to put it to B3 or any designated cell I choose.

Thanks in advance

Fanny

"raypayette" wrote:

Click to show or hide original message or reply text.


You would have to use VBE:
Sub SheetList()
For Each ws In Worksheets
i = i + 1
Cells(i, 1) = ws.Name
Next
End Sub


--
raypayette


------------------------------------------------------------------------
raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
View this thread: http://www.excelforum.com/showthread...hreadid=566968



--

Dave Peterson