With Sheets("wildcard")

[Bishop]

How can I use a wildcard in a With Sheets statement? I have written a macro
that works on sheet ScoreCard. However, during the export process if the end
user saves the workbook rather than open it ScoreCard is changed to
ScoreCard.Bob or ScoreCard.Alice. I need to do something like this to make
the macro work

With Sheets("ScoreCard*")

but that doesn't work. Ideas?

 

[Andrew Taylor]

dim ws as worksheet
for each ws in Worksheets

 

[Dave Peterson]

When I do this for myself, I usually just activate the correct sheet first.

If I do it for others, I tell them to activate the correct sheet first.

Then I can use:

with activesheet

If I want a reminder, I'll add this kind of stuff near the top:

Dim Resp as long

resp = Msgbox(Prompt:="do you want to run the macro against this sheet", _
buttons:=vbyesno)

if resp = vbno then
exit sub
end if

with activesheet
....

 

[Andrew Taylor]

Sorry, posting trouble: try something like

Dim ws as Worksheet
For Each ws in Worksheets
If ws.Name like "ScoreCard*" Then
With ws
' do your stuff
End With
End If
Next

 

[Tom Hutchins]

You could use something like this...

Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
If Left(sht.Name, 9) = "ScoreCard" Then
With sht
'do something
End With
End If
Next sht

Hope this helps,

Hutch