[vba] Checking if sheet exists inside array

BlakeSkate

Well-known Member
Joined
Jan 26, 2015
Messages
516
Office Version
2016
Platform
Windows
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
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,645
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,489
Office Version
365
Platform
Windows
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
 

BlakeSkate

Well-known Member
Joined
Jan 26, 2015
Messages
516
Office Version
2016
Platform
Windows
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,489
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback.

If you try
Code:
If SheetExists(CStr(sheetname)) Then
That should sort the problem.
 

Forum statistics

Threads
1,078,394
Messages
5,339,946
Members
399,342
Latest member
xjmeade

Some videos you may like

This Week's Hot Topics

Top