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?

dim ws as worksheet
for each ws in Worksheets




On 5 May, 17:44, Bishop wrote:
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?

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
....

Bishop wrote:

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?

--

Dave Peterson

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



On 5 May, 17:44, Bishop wrote:

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?

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

"Bishop" wrote:

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?