Insert Tab name into worksheet?

[scewing]

Other than using headers and footers, is there a way to insert the name of a tab into a cell on a worksheet? I know you can insert the filename into a cell using the CELL function, but I can't seem to find a way to insert the tab name into a cell. Thanks in advance!

 

[Dexter77]

Hi

Well I'm taking my second class in VBA - Excel, and I'm still new for such cods. I'll be much appreciated if you are willing to dissect this cods and make easy to understand. Thank you so much for your initiative

Regards...

 

[Dexter77]

Forgot to tell you Smitty that I know how to use this code , but still fresh to know how to write such a code. Thanks

 

[Smitty]

The code is looping through all of the worksheets in the workbook, getting the sheet name and sequentially adding it to the Table of Contents sheets as a hyperlink.

The easiest way to see what it's doing is to step through it and watch each step.

 

[Dexter77]

I see, I'll take your advise and thank you so much, appreciate your king initiative. see u around

 

[smrubin]

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

will strip the tab name (sheet name) out of the full path, if you have an IT dept like mine who frown upon downloading things which make your life much easier

it basically deducts the position of the "]" in the address from the overall length of the address - leaving the amount of characters remaining (which is the sheet name) then applies this number to the RIGHT function of the address, ie counting right to left

HTH
Chris

Thank you! I can always count on these forums!

 

[HDH3rd]

I like that a lot. Did what I needed. Thanks for the work.

 

[ladykishka]

Okay, i'm rather new and i've learned from manipulating someone else's work.

Here is the scenerio that i'm trying to get. I'm trying to do an excel book that has two pages of compilation of the other sheets' information. It is all about money expenditures.

the rest of the book is all information one sheet per person. So here is the question.

I want to have the tabs name to be person 1, person 2, person 3 etc. until i am ready to enter the people's names and keep track of individual information about money they've earned. in A1 i want the 'tab' name (whatever it is at the time)

i know this can be done, i've down loaded books that do exactly what i'm trying to do. But when i copy their formula that is in 'A1' to my sheets it gives me an error.

(further explanation since i'm probably totally confusing everyone)

i'm re-naming Sheet 3 to person 1. A1 on that sheet will now say person 1 (so will other cells on the first two pages) when i find out that Dan Smith will be participating, i will change 'person 1' to 'Dan Smith' and want all the cells that had "='person1'!" to now automatically be changed to "='Dan Smith'! "

the formula that i've been trying to use and has worked in these other books is this:
=MID(CELL("filename",A1),FIND(IF(ISERROR(FIND("]",CELL("filename",A1))),"$","]"),CELL("filename",A1))+1,256)


can anyone either tell me why this works for its book, but not mine, or even better tell me what formula would work for my book so i can make this budgeting thing work more easily for me?

Thank you so much for any help

 

[LadyGrey26]

Thank you so much, this was exactly what I was looking for too! Works like a charm!

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

will strip the tab name (sheet name) out of the full path, if you have an IT dept like mine who frown upon downloading things which make your life much easier

it basically deducts the position of the "]" in the address from the overall length of the address - leaving the amount of characters remaining (which is the sheet name) then applies this number to the RIGHT function of the address, ie counting right to left

HTH
Chris

 

[dw6986]

I don't even have a use for it and that just became my new favorite macro. Have any links to other awe-inspiring macros Smitty?

 

[cdarnold]

Thank you for this awesome trick! Worked perfectly!

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

will strip the tab name (sheet name) out of the full path, if you have an IT dept like mine who frown upon downloading things which make your life much easier

it basically deducts the position of the "]" in the address from the overall length of the address - leaving the amount of characters remaining (which is the sheet name) then applies this number to the RIGHT function of the address, ie counting right to left

HTH
Chris