07-18-2010, 08:47 PM
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.
07-18-2010, 08:57 PM
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!
It works with any 2 existing sheets, not specifically "sheet1 & sheet2"
Anyone ever see this?
04-30-2013, 10:18 AM
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...
For Each wks In .Worksheets
05-02-2013, 02:09 PM
Good catch. I tried the following macro to copy Sheet1 and got a "workbook protected" error message.
This code uses the copy method.
Dim n As Integer
' Creates the new worksheet
Application.ScreenUpdating = False
For Each Sheet In Selection
n = ThisWorkbook.Worksheets.Count
Sheets("Sheet1 (2)").Name = "Sheet" & n + 1
But you can name the copied sheet whatever you want.
The other way is to just add a worksheet:
n = Worksheets.Count
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Sheet" & n + 1
You probably know this but might want optional methods. Either way puts the new worksheet at the end
05-02-2013, 03:52 PM
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:
On Error GoTo ErrHandler
Dim newSheet As Worksheet
Dim buggy As Boolean
buggy = True 'flip this between True/False
If (buggy) Then
Call .Protect("topsecret", True) ' locks structure
Call .Unprotect("topsecret") ' unlocks structure
Set newSheet = .Sheets.Add(After:=Sheets(Sheets.Count)) ' Throws Err?
newSheet.Name = "zippy"
MsgBox "Structure protection: " & .ProtectStructure
' You'll never see, "Structure protection: True"
Exit Sub ' PUNT
'1004 - Method 'Add' of object 'Sheets' failed
MsgBox Err.Number & " - " & Err.Description
05-03-2013, 06:21 AM
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.