+ Reply to Thread
Results 1 to 3 of 3

List Tab names of one excel workbook in another

  1. #1
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Pune
    MS-Off Ver
    Office 365 (Win 10)
    Posts
    489

    List Tab names of one excel workbook in another

    Hi,

    Thanks for all your support till now

    This time i have a different problem

    I need a macro, which will list down the name of tabs which is in a work book in another another work book sheet.

    I found a macro, which will list down that in the same workbook using a formula =sheetname

    But I need the same formula to be used in another workbook, may be using the same formula.

    PHP Code: 
    Function SHEETNAME(Optional ShtOrder As Long 0_
                Optional ByVal IncludeHiddenSheet 
    As Boolean False) As Variant
        
        
    '// Developed by Kris @ ExcelFox.com
        
        Dim ShtCount    As Long
        Dim i           As Long
        Dim n           As Long
        
        ShtCount = ThisWorkbook.Worksheets.Count
        
        If ShtOrder = 0 Then
            SHEETNAME = ActiveSheet.Name
            Exit Function
        End If
        
        SHEETNAME = CVErr(xlErrRef)
        Application.Volatile
        
        With ThisWorkbook
            If IncludeHiddenSheet Then
                If ShtOrder > ShtCount Then Exit Function
                SHEETNAME = .Worksheets(ShtOrder).Name
            Else
                For i = 1 To ShtCount
                    If .Worksheets(i).Visible = -1 Then
                        n = n + 1
                        If n = ShtOrder Then
                            SHEETNAME = .Worksheets(i).Name
                            Exit Function
                        End If
                    End If
                Next
            End If
        End With
        
    End Function 
    Please help

  2. #2
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Pune
    MS-Off Ver
    Office 365 (Win 10)
    Posts
    489

    Re: List Tab names of one excel workbook in another

    Bump....

    Any help pls....

  3. #3
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Pune
    MS-Off Ver
    Office 365 (Win 10)
    Posts
    489

    Re: List Tab names of one excel workbook in another

    I found this code in another site....

    PHP Code: 
    Set NewSheet Sheets.Add(Type:=xlWorksheet)

    For 
    1 To Sheets.Count

        With NewSheet
    .Cells(i1)
            .
    NumberFormat "@"
            
    .Value CStr(Sheets(i).Name)
        
    End With

    Next i 

    But it will list the name of same workbook sheets..

    I needed from another workbook.
    pls helo

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1