CODEWITHSUNDEEP
excelvbaworksheet
user3055889
user3055889

Reputation: 95

Excel VBA Trying to create a new worksheet

I want to create a new worksheet which I can then add data to cell by cell. I have tried the Sheets.Add.Name and it did not work. Currently I get a Run-time error "13" Appreciate any help.

ws.Name = "Sheet1" in this occasion. 

Dim mySheet As Worksheet    
j = -1
i = 0
strCounter = 0
For Each ws In ThisWorkbook.Worksheets
    If (InStr(ws.Name, " Logix Friendly") = 0) Then
        ifChecker = True
    Else
        ifChecker = False
    End If
    If ifChecker Then

    Set mySheet = Workbooks.Add
        With mySheet
            .Nasme = ws.Name, " Logix Friendly"
        End With

Upvotes: 0

Views: 515

Answers (1)

PatricK
PatricK

Reputation: 6433

Tim Williams is absolutely correct about adding a new worksheet.

However your logic appears to be flawed. Your code only works if there isn't a worksheet named "Logix Friendly" and there is only 1 sheet in the workbook.

What you should be doing:

For Each ws In ThisWorkbook.Worksheets
    bWS_Found = (InStr(1, ws.Name, "Logix Friendly", vbTextCompare) = 1)
    ' Exit checking if a worksheet already named "Logix Friendly"
    If bWS_Found Then Exit For
Next
' Add worksheet if there isn't a "Logix Friendly" worksheet
If Not bWS_Found Then
    With ThisWorkbook.Worksheets.Add
        .Name = "Logix Friendly"
    End With
End With

Upvotes: 1

Related Questions