Running a macro on specified worksheets

[Chnaps]

Hi everybody,

I want to run a sub called ColVal on every worksheets of my workbook that start with "Sté". However it seems that my code only runs ColVal on my active worksheet.
Can someone please tell me what I am doing wrong? thank you in advance !

Chnaps



Sub ColValAll()
Dim w As Worksheet
For Each w In Worksheets
If Left(w.Name, 3) = "Sté" Then
Call ColVal
End If
Next w
End Sub

Sub ColVal()
Range("I62:I110").Select
Application.CutCopyMode = False
Selection.Copy
Range("I62").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

 

[mumps]

Try:

Sub ColValAll()
    Application.ScreenUpdating = False
    Dim w As Worksheet
    For Each w In Worksheets
        If w.Name Like "*Sté*" Then
            Call ColVal
        End If
    Next w
    Application.ScreenUpdating = True
End Sub

 

[Chnaps]

Thank you for your quick answer mumps,

Unfortunately your code has the same problem than mine, which is Colval only running on my first worksheet "Parameters"

Let me try to be a little bit more specific about what I am trying to do.
I currently have four worksheets called Sté1 Sté2 Sté3 and Sté4, however this number will certainly change through the year
What I want to do is being able to run my ColVal macro on every worksheet that starts with "Sté" by simply pushing a button
This button, which activates the ColValAll Macro, is on the first worksheet of my workbook and is called "parameters"

Once again, thank you for your help

 

[mumps]

I'm wondering if the accented "e" is creating the problem. Try changing the accented "e" to a plain "e" in your worksheet names and then run:

Sub ColValAll()
    Application.ScreenUpdating = False
    Dim w As Worksheet
    For Each w In Worksheets
        If w.Name Like "*Ste*" Then
            Call ColVal
        End If
    Next w
    Application.ScreenUpdating = True
End Sub

 

[Chnaps]

Found it ! just had to add one line to your code!

Sub ColValAll()    Application.ScreenUpdating = False
    Dim w As Worksheet
    For Each w In Worksheets
        If w.Name Like "*Sté*" Then
            Sheets(w.Name).Select
            Call ColVal
        End If
    Next w
    Application.ScreenUpdating = True
End Sub

 

[mumps]

Glad it worked out.