Excel vba set style in new sheet with a table

Question

How do I rectify this code? I would set style in new sheet but for this code gives 1004 error

Sub test()
Sheets.Add.Name = "new"
ActiveSheet.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(3, 3)) = "test"
ActiveSheet.ListObjects.Add(xlSrcRange, Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(3, 3)), , xlYes, , "TableStyleMedium7").Name = "sk"
End Sub

Answer 1

The only reason I could simulate your error, is in case you already have a sheet named "new" in your workbook. If that's the case, try the following code below:

Sub test()

Dim sht                     As Worksheet
Dim shtNewExists            As Boolean

shtNewExists = False

' loop through workbook sheet to search if "new" already exists
For Each sht In ThisWorkbook.Sheets
    If sht.Name = "new" Then
        shtNewExists = True
    End If
Next sht

' is sheet "new" doesn't exist then create one
If Not shtNewExists Then Sheets.Add.Name = "new"

Set sht = ThisWorkbook.Sheets("new")

sht.Range(sht.Cells(1, 1), sht.Cells(3, 3)) = "test"
sht.ListObjects.Add(xlSrcRange, sht.Range(sht.Cells(1, 1), sht.Cells(3, 3)), , xlYes, , "TableStyleMedium7").Name = "sk"

End Sub