VBA Code to generate a list of specific worksheet names

stimey89

New Member
Joined
Feb 19, 2010
Messages
10
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.
 
Last edited:

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,119
Code:
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

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Supplier One Scorecard</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Supplier Two Scorecard</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Supplier Three Scorecard</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:8.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Scorecard List</p><br /><br />

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.

Code:
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

New Member
Joined
Feb 19, 2010
Messages
10
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

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
stimey89,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,316
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top