If you select more than one sheet in a workbook, and start typing or formatting, that data or formatting will be entered in all the selected sheets, not just the active sheet.
Sheet grouping is a great feature – if you want to type on all the sheets, or add formatting, or whatever. But it’s not so great if you don’t notice that the sheets are grouped, and accidentally work on all the sheets.
It’s easy to forget that you grouped a few sheets – you interrupt your work for a quick phone call, or grab another cup of coffee, and poof! That memory is gone, and the worksheet damage can begin.
Highlight Grouped Sheets
In all the years that I’ve used Excel, the grouped sheets alert hasn’t changed. Excel gives us big, flashy warnings about other things, but not for grouped sheets.
Instead, we only get subtle reminders:
- a tiny “[Group]” at the end of the file name, in the title bar.
- bold text on the selected sheet tabs
And that text in the title bar isn’t highlighted in green, like it is in my screen shot below. Nope, it’s just plain text, and very easy to overlook.
What would you like Excel to do, to make grouped sheets more noticeable?
- Change the title bar to purple?
- Play scary music?
- Show a scrolling message?
Show a Warning
Those features might be in the next version, and in the meantime, you can use a macro that counts the number of sheets that are selected. If the count is higher than 1, the macro shows a warning message, so you will remember to ungroup the sheets.
To use this code, paste it into a regular code module in your Excel file. Then, you could run the code at the start of other macros, or add it to the Workbook_SheetActivate event code.
[Code updated – thanks to Dick Kusleika]
Sub GroupedCount() Dim wdw As Window Dim ws As Worksheet On Error Resume Next Set wdw = ActiveWindow Dim Count As Long Count = wdw.SelectedSheets.Count If Count > 1 Then MsgBox "WARNING! " & Count & " sheets are grouped", _ vbOKOnly + vbCritical, "Grouped Sheets" End If End Sub
Video: Macro Fails If Sheets Grouped
Grouped sheets can also cause problems if you try to run code that only works on ungrouped sheets. For example, if code protects or unprotects all the sheets, the code will fail if the sheets are grouped, as you can see in this video.
To prevent errors, you can ungroup the sheets before running the macro, or add a line of code in the macro, to select just one sheet.