New Worksheet Error

Hi,
I'm trying to insert a new worksheet & I'm getting the error:
"That command cannot be used on multiple selections"

The sheets & workbook are all unprotected. I've confirmed that I'm only selecting 1 of the existing worksheets (not multiple).

New workbooks can add new sheets just fine. It seems to be something specific to this workbook.

Any ideas?
Thanks

OK, here's a strange "fix" ... clicking the new sheet tab causes the error. Shift+sheet1+new sheet causes the error. Shift+sheet1+sheet2+new sheet creates a new sheet!

HUH???

It works with any 2 existing sheets, not specifically "sheet1 & sheet2"

Crazy!!

Anyone ever see this?

I burned many hours on this issue. It turns out that the workbook had some structure protection attributes set on it.

I reused some internally developed corporate code to remedy issue. The key lines are...

With ThisWorkbook
.Unprotect (strPswd)
For Each wks In .Worksheets
wks.Unprotect (strPswd)
Next
End With

chuckthenerd
Good catch. I tried the following macro to copy Sheet1 and got a "workbook protected" error message.

This code uses the copy method.


Sub Addws()
Dim n As Integer
' Creates the new worksheet
Application.ScreenUpdating = False
Sheet1.Select
For Each Sheet In Selection
n = ThisWorkbook.Worksheets.Count
Sheets("Sheet1").Copy After:=Sheets(n)
Sheets("Sheet1 (2)").Select
Sheets("Sheet1 (2)").Name = "Sheet" & n + 1
Next
Range("a1").Select
End Sub


But you can name the copied sheet whatever you want.
The other way is to just add a worksheet:

Sub Addsheet()
n = Worksheets.Count
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Sheet" & n + 1
End Sub


You probably know this but might want optional methods. Either way puts the new worksheet at the end

@cacody
The underlying issue that needed to be resolved in my case was a need to ".Unprotect" the workbook's locked structure before I could add a new sheet.

e.g. Scope out the following:
Sub AddZippy()
On Error GoTo ErrHandler

Dim newSheet As Worksheet
Dim buggy As Boolean

buggy = True 'flip this between True/False

With ThisWorkbook

If (buggy) Then
Call .Protect("topsecret", True) ' locks structure
Else
Call .Unprotect("topsecret") ' unlocks structure
End If

Set newSheet = .Sheets.Add(After:=Sheets(Sheets.Count)) ' Throws Err?

newSheet.Name = "zippy"
MsgBox "Structure protection: " & .ProtectStructure
' You'll never see, "Structure protection: True"

End With

Exit Sub ' PUNT

ErrHandler:
'1004 - Method 'Add' of object 'Sheets' failed
MsgBox Err.Number & " - " & Err.Description
End Sub

chuckthenerd,
Pretty cool. I understood you were pointing out to bhsoundman that the issue might be workbook protection. I was just offering to bhsoundman two ways to add worksheets, assuming the workbook was unprotected.
Your code can be easily modified to insert a password of your choice and toggle the Boolean variable. Thanks for sharing.
Thanks.