Excel VBA check for sheet name
Basically I loop through all sheets in the origin workbook then set Show How can I test if that sheet exists? Something like:
asked Jul 14, 2011 at 3:23
1
Some folk dislike this approach because of an "inappropriate" use of error handling, but I think it's considered acceptable in VBA... An alternative approach is to loop though all the sheets until you find a match.
darcyy 5,1565 gold badges27 silver badges40 bronze badges answered Jul 14, 2011 at 4:27
Tim WilliamsTim Williams 142k8 gold badges91 silver badges118 bronze badges 11 If you are specifically interested in worksheets only, you can use a simple Evaluate call:
answered Feb 12, 2015 at 9:25
RoryRory 31.4k5 gold badges30 silver badges33 bronze badges 10 You don't need error handling in order to accomplish this. All you have to do is iterate over all of the Worksheets and check if the specified name exists:
answered Mar 27, 2013 at 20:21
fbonettifbonetti 6,4643 gold badges33 silver badges32 bronze badges 1 As checking for members of a collection is a general problem, here is an abstracted version of @Tim's answer:
This function can be used with any collection like object ( To check for the existence of a sheet, use
shA.t 16.1k5 gold badges50 silver badges107 bronze badges answered Jan 24, 2013 at 20:11
Peter AlbertPeter Albert 16.6k4 gold badges63 silver badges88 bronze badges 2 I wrote this one:
answered Apr 16, 2018 at 19:24
AOBRAOBR 2492 silver badges4 bronze badges 3 Corrected: Without error-handling:
answered Feb 17, 2015 at 14:07
Shai AlonShai Alon 93513 silver badges20 bronze badges 0 In case anyone wants to avoid VBA and
test if a worksheet exists purely within a cell formula, it is possible using the
This will return answered Jan 7, 2016 at 6:33
Compact wsExists function (without reliance on Error Handling!)Here's a short & simple function that doesn't rely on error handling to determine whether a worksheet exists (and is properly declared to work in any situation!)
Example Usage:The following example adds a new worksheet named
More Information:
answered May 11, 2018 at 16:56
ashleedawgashleedawg 19.4k7 gold badges69 silver badges100 bronze badges My solution looks much like Tims but also works in case of non-worksheet sheets - charts
. answered Aug 3, 2014 at 15:43
uildriksuildriks 511 silver badge1 bronze badge Many years late, but I just needed to do this and didn't like any of the solutions posted... So I made one up, all thanks to the magic of (SpongeBob rainbow hands gesture) "Evaluate()"!
Returns TRUE if Sheet does NOT exist; FALSE if sheet DOES exist. You can substitute whatever range you like for "1:1", but I advise against using a single cell, cuz if it contains an error (eg, #N/A), it will return True. answered Aug 1, 2016 at 16:37
1
Put the test in a function and you will be able to reuse it and you have better code readability. Do NOT use the "On Error Resume Next" since it may conflict with other part of your code.
answered Jan 9, 2014 at 9:26
Martin CarlssonMartin Carlsson 4412 gold badges6 silver badges16 bronze badges Short and clean:
answered Apr 3, 2020 at 4:13
Excel HeroExcel Hero 13.9k4 gold badges28 silver badges38 bronze badges 0
answered Aug 5, 2013 at 10:56
M1NTM1NT 3761 gold badge4 silver badges12 bronze badges Why not just use a small loop to determine whether the named worksheet exists? Say if you were looking for a Worksheet named "Sheet1" in the currently opened workbook.
answered Jan 16, 2015 at 7:55
ScottMcCScottMcC 3,7611 gold badge26 silver badges34 bronze badges
answered Mar 28, 2017 at 12:24
ShrikantShrikant 5134 silver badges15 bronze badges If you are a fan of
Or in a function like this:
answered May 23, 2017 at 9:25
VityataVityata 41.7k7 gold badges52 silver badges89 bronze badges Change "Data" to whatever sheet name you're testing for...
Dan Lowe 47.1k17 gold badges118 silver badges110 bronze badges answered Jul 10, 2017 at 16:54
gth826agth826a 911 silver badge4 bronze badges Without any doubt that the above function can work, I just ended up with the following code which works pretty well:
Note:
Cody Gray♦ 234k50 gold badges482 silver badges559 bronze badges answered Jun 28, 2016 at 2:24
I did another thing: delete a sheet only if it's exists - not to get an error if it doesn't:
answered Feb 17, 2015 at 15:22
Shai AlonShai Alon 93513 silver badges20 bronze badges I use this function to check and return a new sheet name if needed. WSname is the desired worksheet name and WBCur is the workbook you would like to check in. I use this because there is no need for error handling and can call it whenever i am creating a new worksheet.
answered Jul 14, 2021 at 19:51
I came up with an easy way to do it, but I didn't create a new sub for it. Instead, I just "ran a check" within the sub I was working on. Assuming the sheet name we're looking for is "Sheet_Exist" and we just want to activate it if found:
I also added a pop-up for when the sheet doesn't exist. answered Jun 14, 2018 at 15:13
I know it is an old post, but here is another simple solution that is fast.
answered Apr 4, 2019 at 15:39
GuestGuest 4302 silver badges4 bronze badges I actually had a simple way to check if the sheet exists and then execute some instruction: In my case I wanted to delete the sheet and then recreated the same sheet with the same name but the code was interrupted if the program was not able to delete the sheet as it was already deleted
Cody Gray♦ 234k50 gold badges482 silver badges559 bronze badges answered Mar 7, 2014 at 14:47
1 How do I find a specific sheet name in VBA?Search by worksheet name with VBA. Press the Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.. Click Insert > Module, and then paste following VBA code into the new opening Module window.. VBA: Search by worksheet name.. Press the F5 key or click the Run button to run this VBA.. How do you check if a sheet exists in Excel VBA?Here the VBA is formatted as a user defined function. What is this? With this code we can use =WorksheetExists(B3) to test any text string to see if it exists as a sheet name in the current workbook.
How do I search for a sheet name in Excel?Go to a specific sheet with Kutools for Excel. Click Kutools Plus > Worksheet > Create List of Sheet Names.. In the popped out dialog, do as below: ... . Click Ok. ... . Press Ctrl + F to open the Find and Replace dialog, then type the sheet name you want to find into the Find what textbox. ... . Click Find All.. How do I check if a worksheet exists?ISREF. The ISREF function returns a Boolean (TRUE/FALSE) value in respect to its argument, whether it's a valid reference or not. Since a valid reference means that the reference exists, the function can also tell us whether a certain worksheet exists.
|