Test or check if sheet exists

0 votes
Dim wkbkdestination As Workbook
Dim destsheet As Worksheet

For Each ThisWorkSheet In wkbkorigin.Worksheets 
    'this throws subscript out of range if there is not a sheet in the destination 
    'workbook that has the same name as the current sheet in the origin workbook.
    Set destsheet = wkbkdestination.Worksheets(ThisWorkSheet.Name) 
Next

Basically, I iterate through every sheet in the origin workbook by looping through it, then I set the destsheet property in the destination workbook to the sheet with the same name as the iteration's current iteration.

How do I find out if that sheet is real? the following:

If wkbkdestination.Worksheets(ThisWorkSheet.Name) Then 
Oct 27 in Others by Kithuzzz
• 20,660 points
56 views

1 answer to this question.

0 votes

Some people don't like this strategy because it makes "inappropriate" use of error handling, but in VBA, I believe it's fine. Looping through all the sheets until you discover a match is an alternative strategy.

Function WorksheetExists(shtName As String, Optional wb As Workbook) As Boolean
    Dim sht As Worksheet

    If wb Is Nothing Then Set wb = ThisWorkbook
    On Error Resume Next
    Set sht = wb.Sheets(shtName)
    On Error GoTo 0
    WorksheetExists = Not sht Is Nothing
End Function

I hope this helps you.

answered Oct 27 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

How to check if array is multidimensional or not?

Since the 'second dimension' could be just ...READ MORE

answered Nov 5, 2018 in Others by DataKing99
• 8,240 points
4,151 views
0 votes
0 answers

How to check if DynamoDB table exists?

I'm a new user in boto3 and ...READ MORE

Mar 14 in Others by Edureka
• 13,640 points
1,020 views
0 votes
0 answers

SEO - How to programmatically check if a website has been Banned by Google

i wanted to link a website to ...READ MORE

Feb 14 in Others by Kichu
• 19,040 points
81 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,720 points
451 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,218 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9 in Others by gaurav
• 22,040 points
69 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
205 views
0 votes
1 answer

Excel does not show hidden sheet but if i unzip it, then there are 2 worksheets

I now know that an Excel worksheet ...READ MORE

answered Nov 12 in Others by narikkadan
• 37,660 points
32 views
0 votes
1 answer

Spell check an Excel sheet in VBA

Use this code to check the whole ...READ MORE

answered Nov 19 in Others by narikkadan
• 37,660 points
30 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP