How to use VBA code to list specific Tab Names
Category: [Excel] Demo Available
How to use VBA code to list specific Tab Names
Requirement:
The user would like to create a list of specific tab names on a summary worksheet so that the user can use the indirect function to pull data based on the tab name as a reference.
Sheets are added deleted continually so this code would need to clear or refresh the list on sheet activate.
There would be a maximum number of sheets "20" so the user assumes this figure could be a range (B10:B30) limiter for clearing cell contents prior to generating a new list.
All tab names that the user would like listing use the same format "Course-001", "Course-002" etc and would like the list to start in cell B10 on the "Summary" sheet.
https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1195235-list-specific-tab-names
Solution:
Code:
Private Sub Worksheet_Activate()
Dim wsh As Worksheet, x(), k&
ReDim x(1 To 21, 1 To 1)
For Each wsh In ThisWorkbook.Sheets
If InStr(wsh.Name, "Course") Then k = k + 1: x(k, 1) = wsh.Name
Next wsh
Range("B10:B30").Value = x
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by nilem.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use VBA code to colour tabs based on tab/text number |
| How to merge tabs from different excel macro xlsm files into one file |
| How to copy range from variable named workbook to current workbook |
| How to use VBA code to sort worksheets based on a pre-sorted named-range |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.