IF Statement Problem Does not work for Selection

Question

I have tried to make this code but still having an issue.

Question is simple -

If sheet2 exists then 
     ThisWorkbook.Sheets(Sheet1).Range(a1).Select
If does not exist then    
     Set ws = Worksheets.Add(after:=Worksheets("Sheet1")) ws.Name = "Sheet2"

But after adding sheet2 it still gives error. Below is the code:

Sub new6()
    
    Dim wb          As Workbook
    Dim ws          As Worksheet
    Set wb = ActiveWorkbook
    For Each ws In wb.Worksheets
        
        If ws.Name <> "Sheet2" Then
            Set ws = Worksheets.Add(after:=Worksheets("Sheet1"))
            ws.Name = "Sheet2"
        Else
            ThisWorkbook.Sheets(Sheet1).Range(a1).Select
        End If
    Next
End Sub

You are trying to add the sheet before checking ALL of the sheets. You need to find out if the sheet exists in the entire workbook before taking an action — urdearboy, Dec 17 '20 at 17:37
urdearboy, Sir there is single sheet that is Sheet1 and then i add Sheet2 by running the code, then again run the code it keeps adding the sheet with different names i do not know why. https://drive.google.com/file/d/1zy0jImj0Z4WBharSNRfqlKdbopZoQyLw/view?usp=sharing — , Dec 17 '20 at 17:41
What is supposed to happen the second time you run the code? It's doing that because the first sheet in your loop is `Sheet1` which sends you to the sheet creation bit of your code. Although, this should give you an error due to duplicated sheet names. — urdearboy, Dec 17 '20 at 17:41
again, do not post links to google drive - nobody will look. [Edit] your question and add the screen shot there — urdearboy, Dec 17 '20 at 17:42
urdearboy, Sir i want nothing it should return to ThisWorkbook.Sheets(Sheet1).Range(a1).Select to A1 if run the code 100 times — , Dec 17 '20 at 17:43

urdearboy · Accepted Answer · 2020-12-17T17:49:08.577

I think you need to change your approach and remove that loop entirely by implementing a great solution from @Tim Williams. The issue with your code is you are adding a new sheet before verifying if the sheet exists in the book.

Add the WorksheetExists function which will scan the entire workbook for a sheet without a loop and return WorksheetExists = TRUE or FALSE. From there you can simplify your macro by removing the loop and acting on the result of the function.

You can also avoid Select here with Application.Goto

Sub new6()

Dim ws As Worksheet

If WorksheetExists("Sheet2", ActiveWorkbook) Then
    Application.Goto (Sheets("Sheet1").Range("A1"))
Else
    Set ws = Worksheets.Add(After:=Worksheets("Sheet1"))
    ws.Name = "Sheet2"
End If

End Sub

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

urdearboy, Sir thank you so Much for the help. Thank you. – Dec 17 '20 at 17:48 — , Dec 17 '20 at 17:48

Vityata · Answer 2 · 2020-12-17T18:13:59.247

Before selecting the A1 range with ThisWorkbook.Sheets(Sheet1).Range(a1).Select, the correct sheet should be selected on the line before.

The second error is that Sheets collection expects a string. Thus:

ThisWorkbook.Sheets("Sheet1").Select
ThisWorkbook.Sheets("Sheet1").Range("a1").Select

For the code above, put a boolean variable, which tracks whether Sheet2 was found. If it was not found, then create a new Worksheet:

Sub new6()
    
    Dim wb          As Workbook
    Dim ws          As Worksheet
    dim isFound  As Boolean
    
    Set wb = ActiveWorkbook
    For Each ws In wb.Worksheets        
        If ws.Name <> "Sheet2" Then
            'Do Nothing
        Else
            isFound = True
            ThisWorkbook.Sheets("Sheet1").Select
            ThisWorkbook.Sheets("Sheet1").Range("a1").Select
        End If
    Next
    
    If Not isFound Then
        Set ws = Worksheets.Add(after:=Worksheets("Sheet1"))
        ws.Name = "Sheet2"
    End If
    
End Sub

The code above will throw an error, if "Sheet1" does not exist. But you may try to make sure that it exists with a few additional lines.

In general - try to avoid Select - How to avoid using Select in Excel VBA. And whenever having queries about Selecting cells in VBA, use the Macro Recorder, for the first couple of months its help will be useful.

Sir here is the screenshot when i run the code it keeps adding the sheets again which is not correct with the error. https://drive.google.com/file/d/1zy0jImj0Z4WBharSNRfqlKdbopZoQyLw/view?usp=sharing — , Dec 17 '20 at 17:37
Dont provide links to google drives. Update your question with the photo. People are not trusting of random links on the internet for obvious reasons :) — urdearboy, Dec 17 '20 at 17:39
Vityata, Sir you code is still giving an error that is "select method of Range class failed" when i run the code by opening sheet1 it does not give any error when run from sheet2 its gives this error. — , Dec 17 '20 at 17:51
@ShSh - the line selecting the sheet was below the line selecting the cell. Now I have changed them. — Vityata, Dec 17 '20 at 18:00

IF Statement Problem Does not work for Selection

2 Answers2