Explanation of why this one liner works

[sam99car]

Hello,

This command which I found on your forum works-THANK YOU! But why??-it seems C10 has something to do with it. Because if I leave it out ("C10"), each of the worksheet displayed is the last sheet name.

Sub tabname()
'
' tabname Macro
' Macro recorded 5/8/02 by sam99car
'
' Keyboard Shortcut: Ctrl+Shift+N
'

For Each sht In Worksheets
sht.Activate
Range("B4").Select
Application.ActiveCell.FormulaR1C1 = "=proper(MID(CELL(""filename"",C10),FIND(""]"",CELL(""filename""),1)+1,255) & "" fineline --"")"

Next sht
End Sub

[Juan Pablo González]

Are you trying to have cell B4 in all sheets have their Sheet name ?

[sam99car]

Hello,

Yes. For each worksheet, B4 will contain the name of that worksheet.

Thank you in advance.

-sam.

[sam99car]

Hello,

Also, if c10 refers to each worksheet, c10 will contain numeric data, and b4 will be initially blank before I run the macro.

Thanks in advance.

-sam.

[Juan Pablo González]

Why the C10 ? because that formula needs to know "which" sheet is it using. If you don't specify it, it will use the active sheet, that's why the last one is the one that appears... strange, right ?

Try this:

Sub NameSheets()
Dim sht As Worksheet
For Each sht In Worksheets
sht.Range("B4").Formula = "=PROPER(MID(CELL(""filename"",C10),FIND(""]"",CELL(""filename""),1)+1,255) & "" fineline --"")"
Next sht
End Sub

[sam99car]

Hello,

Thank you. The above code works. Previous to your code, I had replaced, C10 with B4 and that displayed a "#name" at B4 for all my worksheets.

Thanks again.

-sam.