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