That command cannot be used on multiple selections error when trying to create a new sheet

CapitalMcmoo

New Member
Joined
May 11, 2010
Messages
3
Hi,

I've built a tool via a pivot table with several macros that work either in the background or via form control buttons and all of a sudden I'm getting an error when I try to create a new tab/sheet in the overall workbook that 'That command cannot be used on multiple selections'.

I've checked pretty thoroughly that there's no multiple selections in play via the following methods:

  • Group and ungroup the tabs
  • Clicked in a separate cell without having the Ctrl key held down
  • Pressed ESC
  • Run a new macro with just 'Application.CutCopyMode = False'
  • Tried to execute a macro with 'Application.DisplayAlerts = False' 'Sheets.Add' 'Application.DisplayAlerts = True' which got still came up with the error
  • Holding Ctrl and moving a current sheet to the end
  • Right clicking a current sheet and selecting Move or Copy and Copy and Move to the End
  • Shift + F11
  • Move or Copy and Copy from a fresh Excel to this sheet
Nothing seems to work. And I'm completely stumped - any ideas?
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
How are you creating the new sheet - in code or manually?
 
Upvote 0
What are the "background macros"? I assume they are event handlers? If so, which ones? (mostly interested in any Activate/Deactivate/NewSheet events.)
 
Upvote 0
Function WorkbookOpen(WorkBookName As String) As Boolean
' returns TRUE if the workbook is open
WorkbookOpen = False
On Error GoTo WorkBookNotOpen
If Len(Application.Workbooks(WorkBookName).Name) > 0 Then
WorkbookOpen = True
Exit Function
End If
WorkBookNotOpen:
End Function
Sub IMEIMCRef()
Application.DisplayAlerts = False
If Not WorkbookOpen("G:\MEDICAL SERVICES MODEL\2010 IME IMC Referrals Spreadsheet.xlsb") Then
Workbooks.Open "G:\MEDICAL SERVICES MODEL\2010 IME IMC Referrals Spreadsheet.xlsb"
newrow = Workbooks("2010 IME IMC Referrals Spreadsheet.xlsb").Sheets("Approved Referrals").Cells(Rows.Count, 1).End(xlUp).Row + 1

For k = 1 To 4
Tx = ThisWorkbook.Sheets("Tool").Range(ThisWorkbook.Sheets("Tool").Cells(1, 22 + k), ThisWorkbook.Sheets("Tool").Cells(1, 22 + k)).Value
Workbooks("2010 IME IMC Referrals Spreadsheet.xlsb").Sheets("Approved Referrals").Cells(newrow, 7 + k) = Tx
Workbooks("2010 IME IMC Referrals Spreadsheet.xlsb").Sheets("Approved Referrals").Activate
Application.CutCopyMode = False
Next k
Exit Sub
ElseIf WorkbookOpen("G:\MEDICAL SERVICES MODEL\2010 IME IMC Referrals Spreadsheet.xlsb") Then
Windows("G:\MEDICAL SERVICES MODEL\2010 IME IMC Referrals Spreadsheet.xlsb").Activate
newrow = Workbooks("2010 IME IMC Referrals Spreadsheet.xlsb").Sheets("Approved Referrals").Cells(Rows.Count, 1).End(xlUp).Row + 1

For k = 1 To 4
Tx = ThisWorkbook.Sheets("Tool").Range(ThisWorkbook.Sheets("Tool").Cells(1, 22 + k), ThisWorkbook.Sheets("Tool").Cells(1, 22 + k)).Value
Workbooks("2010 IME IMC Referrals Spreadsheet.xlsb").Sheets("Approved Referrals").Cells(newrow, 7 + k) = Tx
Workbooks("2010 IME IMC Referrals Spreadsheet.xlsb").Sheets("Approved Referrals").Activate
Application.CutCopyMode = False
Next k
End If
Application.DisplayAlerts = True
End Sub
Sub UndoArrowSearchOne()
Application.ScreenUpdating = False
ActiveSheet.PivotTables("PivotTable4").PivotFields("Doctor").ClearAllFilters
ActiveSheet.PivotTables("PivotTable4").PivotFields("State/NSW-Regional/Sydney Metro").ClearAllFilters
ActiveSheet.PivotTables("PivotTable4").PivotFields("Suburb").ClearAllFilters
ActiveSheet.PivotTables("PivotTable4").PivotFields("Speciality").ClearAllFilters
ActiveSheet.PivotTables("PivotTable4").PivotFields("Sub Speciality").ClearAllFilters
ActiveSheet.PivotTables("PivotTable4").PivotFields("Permanent Impairment").ClearAllFilters
ActiveSheet.PivotTables("PivotTable4").PivotFields("In Current Clinical Practice ").ClearAllFilters
Application.ScreenUpdating = True
End Sub
Sub UndoArrowSearchTwo()
Application.ScreenUpdating = False
ActiveSheet.PivotTables("PivotTable4").PivotFields("Notes").ClearAllFilters
ActiveSheet.PivotTables("PivotTable4").PivotFields("Name").ClearAllFilters
ActiveSheet.PivotTables("PivotTable4").PivotFields("Qualifications").ClearAllFilters
ActiveSheet.PivotTables("PivotTable4").PivotFields("Speciality ").ClearAllFilters
ActiveSheet.PivotTables("PivotTable4").PivotFields("Agency Name").ClearAllFilters
ActiveSheet.PivotTables("PivotTable4").PivotFields("Agency Details").ClearAllFilters
ActiveSheet.PivotTables("PivotTable4").PivotFields("Sub Speciality ").ClearAllFilters
ActiveSheet.PivotTables("PivotTable4").PivotFields("Suburb ").ClearAllFilters
ActiveSheet.PivotTables("PivotTable4").PivotFields("Appointments and Availability").ClearAllFilters
ActiveSheet.PivotTables("PivotTable4").PivotFields("In Current Clinical Practice").ClearAllFilters
ActiveSheet.PivotTables("PivotTable4").PivotFields("IME/IMC").ClearAllFilters
ActiveSheet.PivotTables("PivotTable4").PivotFields("Permanent Impairment").ClearAllFilters
Application.ScreenUpdating = True
End Sub
Private Sub Workbook_Open()
Sheets("Sheet2").Activate
Sheets("Sheet2").Visible = False
Sheets("Tool").Activate
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ActiveWorkbook.ReadOnly = True Then Exit Sub
If Sheets("Sheet2").Visible = False Then Sheets("Sheet2").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

Really weird thing. It errors out at work, but I just tried it at home (I have the enterprise edition of Excel - not sure it that changes things) and I can create new tabs galore without errors.....
 
Upvote 0
None of that code should be triggered by adding a new sheet, so I confess I can't see anything there that would cause the error you are getting.
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top