Results 1 to 3 of 3

Thread: Search secondary workbooks for specific tabs in an ".xlsx" file
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2011
    Posts
    157
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question Search secondary workbooks for specific tabs in an ".xlsx" file

    I normally use a basic code that looks into a secondary workbook and scrolls through all that tabs for me (example scroll below). However, what I just realized was that I'm always running this against other macro enabled files. And earlier when I tried it against an ".xlsx" file, it would not go past the start for the for loop, even though it got in the file fine.

    Can someone suggest another method (code) to use to list out the tabs of a secondary
    ".xlsx"
    file?

    Example scroll I am only successfull with marco enabled files:

    For Each sh In Worksheet
    sh.name
    if sh.name="Sheet1" then
    [do this]
    else
    [do that]
    endif
    Next sh

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,629
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Search secondary workbooks for specific tabs in an ".xlsx" file

    It shouldn't matter whether the workbook is an .xlsm or .xlsx file. As it stands, the code loops through each worksheet within the active workbook (although, it should be Worksheets with an "s", not Worksheet). So you'll need to make sure that the target workbook is active when you run this code. Alternatively, you can avoid having to make sure that your target workbook is the active workbook by assigning it to a variable, and do it this way...

    Code:
        Dim wb As Workbook
        Dim ws As Worksheet
        
        Set wb = Workbooks("Book2.xlsx")
        
        For Each ws In wb.Worksheets
            'etc
            '
            '
        Next ws
    Last edited by Domenic; May 20th, 2019 at 06:25 PM.

  3. #3
    Board Regular
    Join Date
    Apr 2011
    Posts
    157
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search secondary workbooks for specific tabs in an ".xlsx" file

    This worked out great. Thank you for the help and the quick reply. Really appreciate it.

Some videos you may like

User Tag List

Tags for this Thread

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
  •