Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    157
    Thanks
    35
    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 20:57.

  2. #2
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,291
    Thanks
    47
    Thanked 257 Times in 237 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 23:12.

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

    XPDiHard (2014-07-29)

  4. #3
    2 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    157
    Thanks
    35
    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
  •