macro or conditional issue

[frissylea]

i am fairly new to excel, so need help with something that could be easy or maybe can't be done. I want to have the name of the Worksheet (i.e. July 2001) appear in a cell at the top of the document. This workbook has a sheet for each month in a year, each time i copy a sheet to create a new month, I have to change the name of the sheet then make a change to a cell at the top of the document with the same text. Is it possible to set up a macro or something that would read what the sheet name is and insert it into the same shell in every sheet in a workbook??

I hope this is not a dumb question... but am trying to save time have to type this text twice for each sheet.

 

[Anne Troy]

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

 

[frissylea]

On 2002-04-08 13:07, Dreamboat wrote:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,
LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

how do i apply this code.. do i just cut and paste into the cell i want it in?? I do have some knowlege on macros.. but not in excell.. I am familure with macros in WordPerfect only..

 

[Chris Davison]

On 2002-04-08 13:14, frissylea wrote:

On 2002-04-08 13:07, Dreamboat wrote:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,
LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

how do i apply this code.. do i just cut and paste into the cell i want it in?? I do have some knowlege on macros.. but not in excell.. I am familure with macros in WordPerfect only..

Chris,

just type it into the cell you want your sheet tab reference to appear

or

copy the formula and paste special as unicode text

 

[frissylea]

Hi Chris,

just type it into the cell you want your sheet tab reference to appear

or

copy the formula and paste special as unicode text

I tried just pasting into the cell... that didn't work. Maybe I am doing something wrong... all that printed out/or that could be seen in the cell was the code... In other words, the name of the Sheet did not replace the code.

I wonder if there is something missing in it. In my excel file, I am using columns A-F and rows 1-38... in row 3, I have merged all the columns together so there is one long cell. Inside this cell I want to duplicate the name of the sheet. Each sheet in the document will be titled with the Month and the Year (July 2001) which represents the month/year that statistics were taken. All I want to do is repeat the name of the sheet (july 2001) into Cell Row 3 of each sheet. Is the code correct for what I want to do.

Thanks for any help.

_________________
Chris
This message was edited by frissylea on 2002-04-10 04:43

 

[christal]

Hi Chris....

I may have figured out the problem... I too tried the same function and it didn't work! It displayed as text in the cell. Copy and paste in the appropriate cell, what was given to you by Dreamboat and then make sure you put and = before the formula. Make sure you have only one = before the value.

Hope this helps!

Christal

 

[Dave Hawley]

Hi Chris

There is potential problem with the CELL function, in that you must force a re-calulation to get the active sheet name. I use a very simple UDF for this:

Function SheetName(AnyCell As Range)
    SheetName = AnyCell.Parent.Name
End Function

To use this, simply Hold down the Alt key, Push F11, go to Insert>Module and paste in the Code. Then click the top right X to get back into Excel and Save. Now in any cell put

=SheetName(F4)

Where F4 is any cell on the sheet.

 

[frissylea]

On 2002-04-10 05:32, Dave Hawley wrote:
Hi Chris

There is potential problem with the CELL function, in that you must force a re-calulation to get the active sheet name. I use a very simple UDF for this:

Function SheetName(AnyCell As Range)
    SheetName = AnyCell.Parent.Name
End Function

To use this, simply Hold down the Alt key, Push F11, go to Insert>Module and paste in the Code. Then click the top right X to get back into Excel and Save. Now in any cell put

=SheetName(F4)

Where F4 is any cell on the sheet.

I tried this, i get a Compile error/or sintax error...

I tried Crystal's way also, no luck... maybe it is just me... LOL

 

[Chris Davison]

Dreamboats entry sometimes produces a leading space at the front and a space in the middle.... try deleting both or just type it in as seen on screen, it shoud work

 

[Jack in the UK]

Chris

the reason this occurs is the formula is not custom or fresh its copied fro where ever, database or web page it dont matter so paste into dhtml MEANS SPACE ! no way to avoid, bar manul abck up a cell after paste (Ctrl+V)

also can tell by the lay out they are to tidy and mirrored..

Hope that makes sence.. the contence is excellent thou i add and work perfect once you jig them

Rdgs you friend. Jack in the UK