Insert tab name for multiple tabs using vba

[maomaodang]

Hi,

I have 20 excel tabs, which each representing a restaurant outlet. I would like to have the tab name indicating in cell B1 to cell M1 for each tab except this Master tab.

Below is my code:

Sub Insert_Tab_Name()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
If sht.Name <> "Master" Then
Range("B1", "M1").Formula = "=Mid(Cell(""filename"",B1),Find(""]"",Cell(""filename""))+1,255)"
End If
Next sht
End Sub

It works on the active tab, but not for the rest of the tabs ie I have to run the macro 20 times rather than 1 time. Anyone can help to check what's wrong with my formula please? Many thanks!

 

[Norie]

Try this.

Sub Insert_Tab_Name()
Dim sht As Worksheet
    For Each sht In ActiveWorkbook.Worksheets
        If sht.Name <> "Master" Then
            sht.Range("B1", "M1").Formula = "=Mid(Cell(""filename"",B1),Find(""]"",Cell(""filename""))+1,255)"
        End If
    Next sht
End Sub

 

[maomaodang]

Thank you so much! It works perfectly now. May I understand how does the sht in front of the Range make it works?

 

[Osvaldo Palmeiro]

Sub Insert_Tab_Name()
 Dim sht As Worksheet
  For Each sht In ActiveWorkbook.Worksheets
   If sht.Name <> "Master" Then
    sht.Range("B1", "M1").Value = sht.Name
   End If
  Next sht
End Sub

 

[Norie]

Without a worksheet reference Range will refer to a range on the active sheet, by changing it to sht.Range it ensures it refers to a range on the sheet that the sheet object variabe sht refers to.

 

[maomaodang]

Thank you for the clarification. Have a good day ahead!