Excel vba loop through all sheets containing first letter M

user_10622560 0
Viewed 85

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.

excel-2010 excel-formula vba excel
2 Answers

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.

user_51591680

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
user_84665230
Related