Seeking to get worksheet names, ignore active sheet

auto.pilot

Well-known Member
Joined
Sep 27, 2007
Messages
704
I would have sheet names in column A, ignoring the active sheet. How can I do this?


Code:
 Sub GetSheetNames()
Dim ws As Worksheet, nextrow As Long
For Each ws In ThisWorkbook.Worksheets

        nextrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
        ActiveSheet.Range("A" & nextrow) = ws.Name

Next ws

End Sub
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,040
Office Version
  1. 365
Platform
  1. Windows
Maybe this way
Code:
Sub GetSheetNames()
    Dim ws As Worksheet, nextrow As Long
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> ActiveSheet.Name Then
            nextrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
            ActiveSheet.Range("A" & nextrow) = ws.Name
        End If
    Next ws
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,541
Messages
5,529,436
Members
409,877
Latest member
DDhol
Top