Results 1 to 6 of 6

Thread: [vba] Checking if sheet exists inside array
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    468
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default [vba] Checking if sheet exists inside array

    Hello,
    I have this sub from @jmacleary found on this post
    but i thought i would be best to start a new post as the problem has shifted
    So the sub will grab sheets from an array and put the first column of each sheet into one sheet titled "Unknown"
    My problem is that the sheets are now generated based on user needs, so there may not be all 6 sheets depicted in the array.
    I feel mega dumb for not being able to write a code that says if this sheetname exists continue with the code, else do nothing?
    I can't get a custom function that uses boolean to work or anything.
    halp

    Code:
    Sub GetColumnA()
    '
    
    '
    Dim lastRow As Long
    Dim SheetNames As Variant
    SheetNames = Array("MTH", "WP", "MKK", "TTW", "W1", "RHH")
    For Each sheetname In SheetNames
    
    lastRow = Sheets(sheetname).Range("A" & Rows.Count).End(xlUp).Row
        Sheets(sheetname).Range("A2:A" & lastRow).Copy
    destrow = Sheets("Unknown").Range("A" & Rows.Count).End(xlUp).Row + 1
        Sheets("Unknown").Select
        Range("A" & destrow).Select
        ActiveSheet.Paste
    Next sheetname
        
    End Sub
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,590
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    9 Thread(s)

    Default Re: [vba] Checking if sheet exists inside array

    Here's a function you can invoke to determine if a sheet exists.

    Code:
    Function SheetExists(shName As String) As Boolean
    SheetExists = False
    For Each sh In Worksheets
        If sh.Name = shName Then
            SheetExists = True
            Exit For
        End If
     Next sh
    End Function
    You can call it like this:
    Code:
    ........
    SheetNames = Array("MTH", "WP", "MKK", "TTW", "W1", "RHH")
    For Each sheetname In SheetNames 
          If SheetExists(sheetname) Then
              'rest of code
    Last edited by JoeMo; Aug 16th, 2019 at 01:02 PM.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,068
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: [vba] Checking if sheet exists inside array

    How about
    Code:
    Sub GetColumnA()
    '
    
    '
    Dim lastRow As Long
    Dim SheetNames As Variant
    SheetNames = Array("MTH", "WP", "MKK", "TTW", "W1", "RHH")
    For Each sheetname In SheetNames
    If Evaluate("isref('" & sheetname & "'!A1)") Then
       lastRow = Sheets(sheetname).Range("A" & Rows.Count).End(xlUp).Row
           Sheets(sheetname).Range("A2:A" & lastRow).Copy
       destrow = Sheets("Unknown").Range("A" & Rows.Count).End(xlUp).Row + 1
           Sheets("Unknown").Select
           Range("A" & destrow).Select
           ActiveSheet.Paste
    End If
    Next sheetname
        
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    468
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [vba] Checking if sheet exists inside array

    Quote Originally Posted by JoeMo View Post
    Here's a function you can invoke
    this i tried already. I get a ByRef arguement type mismatch on "sheetname" in
    Code:
    If SheetExists(sheetname) Then
    possibly because sheetnames as variant?

  5. #5
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    468
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [vba] Checking if sheet exists inside array

    Quote Originally Posted by Fluff View Post
    How about
    this works perfectly. i didn't think about checking a cell inside a sheet.
    thanks fluff
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,068
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: [vba] Checking if sheet exists inside array

    You're welcome & thanks for the feedback.

    If you try
    Code:
    If SheetExists(CStr(sheetname)) Then
    That should sort the problem.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •