Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: macro or conditional issue

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Oklahoma
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.



  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    Oklahoma
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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..


  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


    :: Pharma Z - Family drugstore ::

  5. #5
    New Member
    Join Date
    Apr 2002
    Location
    Oklahoma
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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:

    Code:
    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.



  8. #8
    New Member
    Join Date
    Apr 2002
    Location
    Oklahoma
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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:

    Code:
    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



  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •