Assemble list of sheet names

[rjplante]

I have a single workbook with several worksheets. I would like to generate a list of names and have them added to my sheet named "Data", in column L which has a header in L1 of "Sheets Names". Each sheet has the tab name listed in cell CA1. I have the code listed below to generate the list of tabs and place them in the Data sheet in column L starting with L2. The code below does not work at the moment, it does not generate the list and deletes the header in L1. I also have the issue that some tabs will be visible and some wont. I only want the visible tabs to have their names added to the list.

Thanks for the help.

Robert

Sub SHEET_NAMES()


Dim s As Worksheet
Dim tabname As Variant


Sheets("Data").Visible = True
Sheets("Data").Range("L2").Select


For Each s In Worksheets


tabname = Range("CA1").Value


Sheets("Data").Range("L2").Select
Range(Selection, Cells(Rows.Count, Selection.Column).End(xlUp)).Select
Selection = tabname


Next s




End Sub

 

[mumps]

Try:

Sub SHEET_NAMES()
    Application.ScreenUpdating = False
    Dim i As Long
    For i = 1 To Sheets.Count
        If Sheets(i).Visible = True Then
            Cells(Rows.Count, "L").End(xlUp).Offset(1, 0) = Sheets(i).Name
        End If
    Next
    Application.ScreenUpdating = True
End Sub

 

[rjplante]

Great Thanks!

 

[mumps]

You are very welcome.