[vba] Checking if sheet exists inside array

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
756
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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,692
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
35,705
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
 

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
756
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
35,705
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,089,622
Messages
5,409,381
Members
403,260
Latest member
ssauk

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top