CODEWITHSUNDEEP
excelvba
Satish
Satish

Reputation: 3

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

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 ?

Upvotes: 0

Views: 523

Answers (2)

Michał Turczyn
Michał Turczyn

Reputation: 37377

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

Upvotes: 1

Error 1004
Error 1004

Reputation: 8220

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

Upvotes: 1

Related Questions