CODEWITHSUNDEEP
excelvba
user14807564
user14807564

Reputation:

IF Statement Problem Does not work for Selection

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

Upvotes: 1

Views: 88

Answers (2)

Vityata
Vityata

Reputation: 43575

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.

Upvotes: 0

urdearboy
urdearboy

Reputation: 14580

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

Upvotes: 1

Related Questions