Prev: transfer data range from criteria in 2 combo boxes
Next: Last Column With Data
From: Bishop on 5 May 2010 12:44
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?
From: Andrew Taylor on 5 May 2010 13:01
dim ws as worksheet
for each ws in Worksheets




On 5 May, 17:44, Bishop <Bis...(a)discussions.microsoft.com> 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?

From: Dave Peterson on 5 May 2010 13:05
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
From: Andrew Taylor on 5 May 2010 13:06
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 <Bis...(a)discussions.microsoft.com> 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?

From: Tom Hutchins on 5 May 2010 13:45
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?
 | 
Pages: 1
Prev: transfer data range from criteria in 2 combo boxes
Next: Last Column With Data