[vba] Checking if sheet exists inside array

[DataBlake]

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

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

 

[JoeMo]

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

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:

........
SheetNames = Array("MTH", "WP", "MKK", "TTW", "W1", "RHH")
For Each sheetname In SheetNames 
      If SheetExists(sheetname) Then
          'rest of code

 

[Fluff]

How about

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

 

[DataBlake]

Here's a function you can invoke

this i tried already. I get a ByRef arguement type mismatch on "sheetname" in

If SheetExists(sheetname) Then

possibly because sheetnames as variant?

 

[DataBlake]

How about

this works perfectly. i didn't think about checking a cell inside a sheet.
thanks fluff

 

[Fluff]

You're welcome & thanks for the feedback.

If you try

If SheetExists(CStr(sheetname)) Then

That should sort the problem.