At the end of following code, I would like to go to a specific range in a specific worksheet;

Sub ListSheetNames()
    Dim Sheetname() As String
    Dim SheetCounter As Integer
    Dim idx As Range
    
    ReDim Sheetname(Worksheets.Count)
    Set idx = Sheets("Cover").Range("A1:X1000").Find("Index_Sheets")
    
    For SheetCounter = 1 To Worksheets.Count
        Sheetname(SheetCounter) = Worksheets(SheetCounter).Name
    Next SheetCounter

    Worksheets(1).Select

    For SheetCounter = 1 To Worksheets.Count
        idx.Offset(SheetCounter, 1) = Sheetname(SheetCounter)
    Next SheetCounter
End Sub

For which I used following line before End Sub;

Worksheets("Inputs").Range("C5").Select

But this doesn't work. How can I rewrite this?

At the end of following code, I would like to go to a specific range in a specific worksheet;

Sub ListSheetNames()
    Dim Sheetname() As String
    Dim SheetCounter As Integer
    Dim idx As Range
    
    ReDim Sheetname(Worksheets.Count)
    Set idx = Sheets("Cover").Range("A1:X1000").Find("Index_Sheets")
    
    For SheetCounter = 1 To Worksheets.Count
        Sheetname(SheetCounter) = Worksheets(SheetCounter).Name
    Next SheetCounter

    Worksheets(1).Select

    For SheetCounter = 1 To Worksheets.Count
        idx.Offset(SheetCounter, 1) = Sheetname(SheetCounter)
    Next SheetCounter
End Sub

For which I used following line before End Sub;

Worksheets("Inputs").Range("C5").Select

But this doesn't work. How can I rewrite this?

• excel
• vba

• 2 Application.Goto ThisWorkbook.Worksheets("Dest").Range("C5") for example – Tim Williams Commented Mar 27 at 21:27

1 Answer 1

List Worksheet Names

• You could simplify by using a For Each...Next loop and offsetting the cell on each iteration.

Sub ListSheetNames()
    
    ' Reference the workbook and the destination worksheet.
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim dws As Worksheet: Set dws = wb.Sheets("Cover")
    
    ' Reference the header cell.
    Dim dcell As Range
    With dws.UsedRange ' instead of 'With dws.Range("A1:X1000")'!?
        Set dcell = .Find(What:="Index_Sheets", _
            After:=.Cells(.Cells.CountLarge), LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByRows) ' top leftmost occurrence
    End With
    If dcell Is Nothing Then Exit Sub ' header cell not found
    Set dcell = dcell.Offset(, 1) ' one cell to the right!?

    ' Clear existing sheet names (assuming no data below).
    'dcell.Resize(dws.Rows.Count - dcell.Row).Offset(1).Clear
    
    ' Loop through the worksheets collection and on each iteration,
    ' reference the next cell (below) and write the worksheet name to it.
    Dim sws As Worksheet
    For Each sws In wb.Worksheets
        Set dcell = dcell.Offset(1) ' next row (cell)
        dcell.Value = sws.Name
    Next sws
    
    ' Select a specific cell.
    wb.Activate ' Make sure the workbook is active, ...
    wb.Sheets("Inputs").Select ' ... then the correct sheet is selected...
    wb.Sheets("Inputs").Range("C5").Select ' ... before selecting the cell.
    
    ' The following is almost the same but it only activates the sheet,
    ' i.e., if multiple sheets are selected, they remain selected.
    'Application.Goto wb.Sheets("Inputs").Range("C5")
    
End Sub

• The downside of offsetting the cell is that you don't know how many sheet names were added. If you need this number for e.g. displaying it in a message box, implement a counter as you did in your post:

    Dim sws As Worksheet, SheetsCount As Long
    For Each sws In wb.Worksheets
        SheetsCount = SheetsCount + 1
        dcell.Offset(SheetsCount).Value = sws.Name
    Next sws