Excel vba loop throuhg all sheets containing first letter M

i've been trying to loop through a certain sheet number , i have 20 sheets that start all with M , like M1 , M2 and so on till M20 , my problem is that i'm looping and writing , but instead of writing only on the M sheets , i'm writing in all sheets.

Sub CountWSNames()
        Dim I As Long
        Dim xCount As Integer
        For I = 1 To ActiveWorkbook.Sheets.Count
            If Mid(Sheets(I).Name, 1, 1) = "M" Then xCount = xCount + 1

            ThisWorkbook.Worksheets(I).Range("A50") = "V" 'This line must write only to M sheets
        Next
        MsgBox "There are " & CStr(xCount) & " sheets that start with 'M'", vbOKOnly, "KuTools for Excel"
    End Sub

Small piece of code similar to mine.

ThisWorkbook.Worksheets(I).Range("A50") = "V"

This line must only affect the Cell ("A50") on sheets starting with M.

2 answers

  • answered 2018-11-08 08:34 Andreas

    You can use for each.

    For each sht in ActiveWorkbook.Sheets
        If Mid(sht.Name, 1, 1) = "M" Then 
            xCount = xCount +1
            sht.Range("A50") = "V" 'This line must write only to M sheets
        End if
    Next
    

    You need to wrap both syntaxes inside the if.

  • answered 2018-11-08 08:45 Olly

    If you don't care about the sheet count, but just want to write a value to those sheets, then you can use:

    Sub WriteToMSheets()
        Dim ws as Worksheet
        For Each ws in Thisworkbook.Worksheets
            If Ucase(Left(ws.Name,1)) = "M" Then ws.Range("A50").Value = "V"
        Next ws
    End Sub