Worksheets-Excel Only

Checking the worksheets in a workbook is necessary in many VBA operations. For many VBA macros it is necessary to add, copy, rename, and delete worksheets on a workbook. Excel requires unique worksheet names in each workbook. Although VBA macros can interface with the user to request the disposition of the worksheet name, the macro should execute independently.

One technique to give the user control of the VBA macro is to start with the selection of a worksheet. This worksheet can contain key values for equations or control the flow of the macro. Generating a prompt to select the sheet from the actual list of sheets in the workbook is user friendly.

SAS Fifth Dimension Macro-List sheets in workbook

Review the following code, copy it to Excel and execute it.

Sub LIST_SHEETS_OF_ACTIVE_BOOK()
    Dim SHEET_NAMES(100)
    BOOK_NAME = ActiveWorkbook.NAME
    SHEET_COUNT = Worksheets.Count
    For II = 1 To SHEET_COUNT
        SHEET_NAMES(II) = Worksheets(II).NAME
    Next
    Workbooks.Add
    For II = 1 To SHEET_COUNT
        Cells(II, 1) = SHEET_NAMES(II)
    Next
    Cells(1, 1).Select
    Selection.Insert Shift:=xlDown
    Cells(1, 1) = "Sheets in workbook " & BOOK_NAME
    Columns("A:A").EntireColumn.AutoFit
    ActiveWorkbook.Saved = True
ENDIT:
End Sub