Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    157
    Thanks
    34
    Thanked 3 Times in 2 Posts

    Reversal of a, Add-Sheet-Name Function

    The current Function is: ( I don't remember who wrote it, was ages ago, much appreciated)

    Option Explicit
    Code:
     
    Sub add_Sheet_name()
        Dim sShtName As String
         
        sShtName = ActiveSheet.Cells(1, 2).Value
         
    '    If Not WksExists(sShtName) Then Worksheets.Add After:=Worksheets(Worksheets.Count)
         
        ActiveSheet.Name = sShtName
    End Sub
    Function WksExists(wksName As String) As Boolean
        On Error Resume Next
        WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
    End Function
    It works fine, but I now require the reversal.

    So if the current open worksheet's Tab name is; TestABC

    Then the Function would be to have that Tab name in another sheet's cell, ( Sheet5 Range "A1") of the same opened Workbook.

    I tried reversing the codes but not sure of the syntaxes etc.

    Thanks.
    Last edited by XPDiHard; 2014-07-28 at 19:57.

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,215
    Thanks
    46
    Thanked 231 Times in 212 Posts
    XP,

    How about this subroutine:

    Code:
    Public Sub SheetName()
    Worksheets("Sheet5").Range("A1") = ActiveSheet.Name
    End Sub
    Last edited by Maudibe; 2014-07-28 at 22:12.

  4. The Following User Says Thank You to Maudibe For This Useful Post:

    XPDiHard (2014-07-28)

  5. #3
    2 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    157
    Thanks
    34
    Thanked 3 Times in 2 Posts
    Thanks Maud,
    so obvious,
    = ActiveSheet.Name

Posting Permissions

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