VBA Code to generate a list of specific worksheet names

[stimey89]

I have a workbook that is used to track suppliers' performance. The data collected there is then used to produce a "scorecard" that is presented to the suppliers twice yearly. The workbook has worksheets with the suppliers' names on them, and additional worksheets that are labeled with their names and the word "scorecard" in the tab title.

Example: Sheet 1 is named: "Supplier One".
Data input into "Supplier One" is used to populate Sheet 2 named: "Supplier One Scorecard"

I currently use the VB script below to generate a list of all sheet names in the workbook onto another page, which I then use to create a summary report of all suppliers' by performance ranking:

Sub ListSheetz()
Dim i%
For i = 1 To Sheets.Count
Cells(i, 1).Value = Sheets(i).Name
Next i
End Sub

Is there a way to change or replace this script so that it will generate a list of only those pages containing the word "Scorecard" in the name?

Thanks in advance for any help that is offered.

 

[sheetspread]

Sub ListSheetz()
Dim i%, j%
j = 1
For i = 1 To Sheets.Count
If Sheets(i).Name Like "*Scorecard*" Then
Cells(j, 1).Value = Sheets(i).Name
j = j + 1
Else
End If
Next i
End Sub

 

[hiker95]

stimey89,

My test workbook contained the following worksheets:
Supplier One
Supplier One Scorecard
Supplier Two
Supplier Two Scorecard
Supplier Three
Supplier Three Scorecard

After the macro in a new worksheet Scorecard List:

Excel 2007
	A
1	Supplier One Scorecard
2	Supplier Two Scorecard
3	Supplier Three Scorecard
4
Scorecard List

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ListScorecardSheets()
' hiker95, 02/03/2014, ME755034
Dim ws As Worksheet, sc As Variant, i As Long, n As Long
Application.ScreenUpdating = False
n = Sheets.Count
ReDim sc(1 To n, 1 To 1)
For Each ws In ThisWorkbook.Worksheets
  If ws.Name Like "*Scorecard" Then
    i = i + 1
    sc(i, 1) = ws.Name
  End If
Next ws
If i > 0 Then
  If Not Evaluate("ISREF('Scorecard List'!A1)") Then Worksheets.Add().Name = "Scorecard List"
  With Sheets("Scorecard List")
    .UsedRange.ClearContents
    .Range("A1").Resize(UBound(sc, 1), UBound(sc, 2)) = sc
    .Columns(1).AutoFit
    .Activate
  End With
End If
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ListScorecardSheets macro.

 

[stimey89]

Thank you both very much. Both scripts worked and allowed me to accomplish what I was attempting to do. The expertise and support offered by the members of this board is always much appreciated.

 

[hiker95]

stimey89,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.