VBA For Extract A List Of Worksheet Tab Names

The provided instructions explain how to create a macro in Excel that will extract a list of worksheet tab names. Here's the macro code that you can use:

Dim NewSheet As Worksheet

Set NewSheet = Sheets.Add(Type:=xlWorksheet)

For i = 1 To Sheets.Count

                    With NewSheet.Cells(i, 1)

                                        .NumberFormat = "@"

                                        .Value = CStr(Sheets(i).Name)

                    End With

Next i

 

To implement this macro in your workbook, follow these steps:

1.       Add a command button to your worksheet by going to the Developer tab, clicking on "Insert" in the ActiveX Controls group, and selecting "Command Button."

2.       Resize and rename the command button as desired.

3.       Right-click on the command button and select "View Code" to open the Visual Basic Editor (VBA).

4.       In the VBA editor, find the CommandButton1_Click() subroutine and insert the provided code between Private Sub CommandButton1_Click() and End Sub.

Private Sub CommandButton1_Click()

Dim NewSheet As Worksheet

Set NewSheet = Sheets.Add(Type:=xlWorksheet)

For i = 1 To Sheets.Count

                    With NewSheet.Cells(i, 1)

                                        .NumberFormat = "@"

                                        .Value = CStr(Sheets(i).Name)

                    End With

Next i

End Sub

 

5.       Close the VBA editor and exit Design Mode by clicking on the Design Mode button in the Developer tab.

6.       Save your workbook as a "Macro-Enabled Workbook" by going to File > Save As and selecting the appropriate file type (*.xlsm).

Now, when you click on the command button, a new sheet will be added to your workbook with a list of all the worksheet tab names.