Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
List of worksheets | New Users to Excel | |||
Get a list of worksheets from a WB | Excel Discussion (Misc queries) | |||
Sub to aquire list of worksheets | Excel Programming | |||
Get a List of Worksheets | Excel Programming | |||
VBA list all worksheets | Excel Programming |