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