Sub to aquire list of worksheets
 

AA2e72E Wrote:
Sub xx()
Range("A1").Select
For Each Sheet In ActiveWorkbook.Sheets
Select Case Sheet.Name
Case "EG1"
'Ignore
Else
ActiveCell.Value = Sheet.Name
Cells(Cells.Row + 1, Cells.Column).Activate
End Select
Next
End Sub

Else Without If


Code
-------------------

Sub MacroAddNewFileListSheetNames()
' Creates a list of names of the sheets in SHEET Master Numbers, Column I (emitts Master Numbers)

Range("I1").Select
For Each Sheet In ActiveWorkbook.Worksheets
Select Case Sheet.Name
Case "Master Numbers"
'Ignore
Else
ActiveCell.Value = Sheet.Name
Cells(Cells.Row + 1, Cells.Column).Activate
End Select
Next

End Su
-------------------

--
aking198
-----------------------------------------------------------------------
aking1987's Profile: http://www.excelforum.com/member.php...fo&userid=1539
View this thread: http://www.excelforum.com/showthread.php?threadid=31434

Sub to aquire list of worksheets
 

Oops!

Replace the line

Else

by

Case Else

"aking1987" wrote:


AA2e72E Wrote:
Sub xx()
Range("A1").Select
For Each Sheet In ActiveWorkbook.Sheets
Select Case Sheet.Name
Case "EG1"
'Ignore
Else
ActiveCell.Value = Sheet.Name
Cells(Cells.Row + 1, Cells.Column).Activate
End Select
Next
End Sub

Else Without If


Code:
--------------------

Sub MacroAddNewFileListSheetNames()
' Creates a list of names of the sheets in SHEET Master Numbers, Column I (emitts Master Numbers)

Range("I1").Select
For Each Sheet In ActiveWorkbook.Worksheets
Select Case Sheet.Name
Case "Master Numbers"
'Ignore
Else
ActiveCell.Value = Sheet.Name
Cells(Cells.Row + 1, Cells.Column).Activate
End Select
Next

End Sub
--------------------


--
aking1987
------------------------------------------------------------------------
aking1987's Profile: http://www.excelforum.com/member.php...o&userid=15393
View this thread: http://www.excelforum.com/showthread...hreadid=314346

Sub to aquire list of worksheets
 

The full solution (not e change from CElls.Row to ActiveCell.Row etc.):

Sub xx()
Range("A1").Select
For Each Sheet In ActiveWorkbook.Sheets
Select Case Sheet.Name
Case "EG1"
'Ignore
Case Else
ActiveCell.Value = Sheet.Name
Cells(ActiveCell.Row + 1, ActiveCell.Column).Activate
End Select
Next
End Sub



"AA2e72E" wrote:

Oops!

Replace the line

Else

by

Case Else

"aking1987" wrote:


AA2e72E Wrote:
Sub xx()
Range("A1").Select
For Each Sheet In ActiveWorkbook.Sheets
Select Case Sheet.Name
Case "EG1"
'Ignore
Else
ActiveCell.Value = Sheet.Name
Cells(Cells.Row + 1, Cells.Column).Activate
End Select
Next
End Sub

Else Without If


Code:
--------------------

Sub MacroAddNewFileListSheetNames()
' Creates a list of names of the sheets in SHEET Master Numbers, Column I (emitts Master Numbers)

Range("I1").Select
For Each Sheet In ActiveWorkbook.Worksheets
Select Case Sheet.Name
Case "Master Numbers"
'Ignore
Else
ActiveCell.Value = Sheet.Name
Cells(Cells.Row + 1, Cells.Column).Activate
End Select
Next

End Sub
--------------------


--
aking1987
------------------------------------------------------------------------
aking1987's Profile: http://www.excelforum.com/member.php...o&userid=15393
View this thread: http://www.excelforum.com/showthread...hreadid=314346

Sub to aquire list of worksheets
 

!Same as below!

Sub xx()
Range("A1").Select
For Each Sheet In ActiveWorkbook.Sheets
Select Case Sheet.Name
Case "EG1"
'Ignore
Case Else
ActiveCell.Value = Sheet.Name
Cells(ActiveCell.Row + 1, ActiveCell.Column).Activate
End Select
Next
End Sub



"aking1987" wrote:


AA2e72E Wrote:
Sub xx()
Range("A1").Select
For Each Sheet In ActiveWorkbook.Sheets
Select Case Sheet.Name
Case "EG1"
'Ignore
Else
ActiveCell.Value = Sheet.Name
Cells(Cells.Row + 1, Cells.Column).Activate
End Select
Next
End Sub

Else Without If


Code:
--------------------

Sub MacroAddNewFileListSheetNames()
' Creates a list of names of the sheets in SHEET Master Numbers, Column I (emitts Master Numbers)

Range("I1").Select
For Each Sheet In ActiveWorkbook.Worksheets
Select Case Sheet.Name
Case "Master Numbers"
'Ignore
Else
ActiveCell.Value = Sheet.Name
Cells(Cells.Row + 1, Cells.Column).Activate
End Select
Next

End Sub
--------------------


--
aking1987
------------------------------------------------------------------------
aking1987's Profile: http://www.excelforum.com/member.php...o&userid=15393
View this thread: http://www.excelforum.com/showthread...hreadid=314346