VBA - Excel 97 - Use a sheet name as a value in a cell

  • Thread starter Thread starter Jim@gems
  • Start date Start date
J

Jim@gems

Can anyone please help?

I'm trying to write a piece of code that will use the names of sheets
in a workbook as values in each individual sheet as a heading ( Title
)


if Sheet1(Sales),Sheet2(Purchases),Sheet3(Products)


Therefore the Cell C2 in each sheet will have the above names.

i.e. Sheet1 C2 = Sales
Sheet2 C2 = Purchases
Sheet3 C2 = Products:)
 
You want C2 of each sheet to get the name of the worksheet?

as a macro:

Option Explicit
Sub testme()
Dim wks As Worksheet

For Each wks In Worksheets
With wks
.Range("C2").Value = "'" & .Name
End With
End With

End Sub

But you could use a formula, too:

From Debra Dalgleish's site:
http://www.contextures.com/xlfaqFun.html#SheetName

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

will strip away everything but the sheet name.
(but the workbook has to be saved first.)

And with the formula, you won't have to worry if the sheet names. When the
worksheet recalcs, it'll be there.
 
Back
Top