VBA - replace cell value with sheet name for sheets beginning with a certain letter

[athaung]

Hi Everyone,

I've been reviewing various posts from this forum to piece together a solution, but was unsuccessful.

I have a workbook with a bunch of sheets and I am interested in the sheets that begin with the letter "P". Each of these sheets is named "P###" with #s ranging from 0-9 and not consecutively.

On each of these sheets, the various formulas link to the cell O37. I would like to replace the value in cell O37 with the name of the sheet "P###" for all sheets that begin with P.

Thanks in advance for any help!

Alison

 

[AlphaFrog]

Hi and welcome to the forum. Try this...

[color=darkblue]Sub[/color] P_Sheets()
    [color=darkblue]Dim[/color] ws [color=darkblue]As[/color] Worksheet
    [color=darkblue]For[/color] [color=darkblue]Each[/color] ws [color=darkblue]In[/color] Worksheets
        [color=darkblue]If[/color] UCase(ws.Name) [color=darkblue]Like[/color] "P###" [color=darkblue]Then[/color]
            ws.Range("O37").Value = ws.Name
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[athaung]

That worked like a charm. Thank you for your quick and very helpful response!