Code to get worksheet name into a cell

[Hydestone]

Is it possible to have the worksheet name autopopulate into a cell?

Example - I want the sheet name to appear in cell F1. Is that possible? Also, if I rename Sheet 1 to 12345, the info in cell F1 would change from Sheet 1 to 12345.

 

[kweaver]

Put this formula in F1:

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

 

[Kamolga]

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

 

[Hydestone]

Nice thanks to both of you!

 

[Hydestone]

For some reason, when I copy the worksheet which is using the reference above, the new worksheet displays #VALUE in the cell, instead of the sheet name. If I save the file, it still says #VALUE . If I save, close, and reopen - the formula then works and the cell shows the worksheet name. Any way to get the sheet name to show without closing and reopening the workbook?

 

[kweaver]

Do you have calculations set to manual or Automatic?

 

[Fluff]

Are you copying the sheet to a brand new workbook?

 

[kweaver]

Fluff hit the nail on the head, of course. The CELL("filename", A1) finds the full path...but if you haven't saved the workbook, there is no path. If you save it, does the sheet name update correctly?

 

[Hydestone]

Yes, copying to new workbook. When I click save, nothing changes. When I close the book and then reopen, the information populates in the cell.

Any way to make it populate without closing and reopening?

 

[Fluff]

After saving press F9, does that make a difference?