Worksheets.Add does not work with ( )

[lastnn30]

Hi

Why when I use ( ) for the Add method, I got error message? It only works without ( ). I am just trying to experiment how and when to use ( ) and when not. Thank you.

 Worksheets.Add (before:=Worksheets(x))

Sub my_add_sheet3()
    
    Dim x As Integer
    x = InputBox("Add before which sheet? Enter sheet number")
    Worksheets.Add before:=Worksheets(x)
    
End Sub

[Trebor76]

Hi lastnn30,

What error message do you get? If (as I suspect) it's "Run-time error 9" it's either because you have not assigned any positive value to x or the number is more than the actual sheets in the workbook.

IMO I would not use the extra brackets as you have done in your first macro as they are not needed and it makes it easier to review without them.

Regards,

Robert

[lastnn30]

Thank you. The message is "add ="

[Trebor76]

Thank you. The message is "add ="

Yes it seems you have to remove the extra brackets (parentheses).

[romperstomper]

You only use parentheses if:
1. You are using Call; or
2. You are storing the result of a function/method; or
3. You are explicitly trying to evaluate/dereference an argument.

[ByteMarks]

For example, if you wanted to assign the added worksheet to a variable, you'd add the brackets.

Sub my_add_sheet3()
    
    Dim x As Integer
    x = InputBox("Add before which sheet? Enter sheet number")
    Set NewWorksheet = Worksheets.Add(before:=Worksheets(x))
    
End Sub