Excel Macro for Changing cell value to match the name of the worksheet

Question

I have around 250 sheets in my Excel workbook. I would like to change the value of cell B2 in each of the sheets to match the name of their respective worksheet.

How do I go about doing this using a VBA based code ?

Answer 1

Try:

Option Explicit

Sub Get_Sheets_Name()

    Dim ws As Worksheet

    'Loop all sheets in this workbook
    For Each ws In ThisWorkbook.Worksheets

        With ws
            'Import sheet name in B2
            .Cells(2, 2).Value = ws.Name
        End With

    Next

End Sub

Answer 2

Well, you could automate it little bit further than in accepted answer.

Let's consider: if you change any name of worksheet, you'll have the same problem.

So if you'd enter formula in cell B2 to retrieve sheet name, changing name of a worksheet would automatically update cell content as well, opposite to given answer, where you'd need to run the code again and again.

So, consider using such code (once for a lifetime :) ):

Sub Get_Sheets_Name()
    Dim ws As Worksheet
    'Loop all sheets in this workbook
    For Each ws In ThisWorkbook.Worksheets
        ws.Cells(2, 2).Formula = "=MID(CELL(""filename"",A1),FIND(""]"",CELL(""filename"",A1))+1,256)"
    Next
End Sub