Merging: displaying which sheet data comes from when merging sheets

There are two kinds of Excel people - The ones who use formulas in the spreadsheet exclusively and the ones who use VBA exclusively. Ideally - the complete excel programmer should be well versed in both.

But I'm a VBA person and here's how I'd get the sheet name:

Sub SheetName()
Dim S As String
S = ActiveSheet.Name
MsgBox (S)
End Sub

I've seen formulas in the sheet that also display the name but I didn't save them.

Hopefully, a formula person or a Compleat Excel Programmer will also hit on this thread.

I found it:

=MID(CELL("filename",A1),SEARCH("]",CELL("filename",A1),1)+1,LEN(CELL("filename",A1)))

which I doctored from:

CELL("filename",A1)

which gives you the fullname.