What it does
Uses a settings sheet (Bulk_Sheet_Update) to loop through a defined list of sheets, for which different settings can be adjusted, including renaming, reordering, visibility, tab color, where to freeze the panes, the zoom level, page layout type, whether to show gridlines and headings, and whether to delete sheets.  To apply even more settings to multiple sheets, consider using the Multi Sheet Format macro.

When to use it
When you want to quickly apply numerous sheet settings to several sheets in one bulk step.

Why to use it
It’s an efficient way to apply numerous common sheet settings to several sheets in one bulk step, ensuring each step is completed consistently and accurately.  This is especially useful when you apply the same updates to the same sheets repetitively, such as in a report you download where each sheet represents a department, location, and region.

Default shortcut
None

Other Details

• Category: Sheets / Multi-sheets
• Difficulty: 4/5
• Usage/frequency: 2/5
• Automation factor: 5/5 (estimated 300 seconds saved each time used)
• Type: Bulk
• Date added: 8/5/2019
• Tags: sheets, bulk, rename, reorder

Related Macros
Sheet Action Picker
Bulk Sheet Rename
Bulk Sheet Reorder
Multi Sheet Format

Other Articles
None

Bulk Sheet Update – Example File

Prerequisites
Identify the sheet(s) you want to update.

Instructions
After you have identified the sheet(s) you want to update, run the Bulk Sheet Update macro.  The first time you run it, it will create a sheet called Bulk_Sheet_Update, and the current sheet names, order, and other settings will be listed.  This is where you’ll configure all the sheets you want to update.  These are the columns within the Bulk_Sheet_Update sheet you’ll want to fill in:

• Column A – Old sheet name (required): enter the existing sheet name to update.  If adding a new sheet, enter “[NEW]” as the old sheet name, then enter the name of the new sheet.
• Column B – Old sheet order (required): do not change the old sheet order, otherwise your workbook could have unintended changes.
• Column C – New sheet name (optional): to change the sheet name, enter a different, unique sheet name.
• Column D – New sheet order (optional): to change the sheet order, enter a different, unique sheet order. If a new sheet order is not entered, it will be skipped, but the order may change based on changing the order of other sheets.
• Column E – Visibility (optional): enter Show to show the sheet or Hide to hide the sheet.  If not entered, the existing state is not changed (or if a new sheet is added, it is set to visible).
• Column F – Tab color (optional): use the fill/highlight color to set the tab color for the sheet.
• Column G – Freeze panes range (optional): set the range where you want freeze panes applied in the sheet.
• Column H – Zoom level (optional): enter the zoom level to apply for the sheet (between 10-400).
• Column I – Page layout (optional): enter the page layout type to apply for the sheet (normal, page break, or page layout).
• Column J – Show gridlines (optional): enter Show to show the gridlines, or Hide to hide the gridlines.  If not entered, the existing state is not changed (or if a new sheet is added, it is set to show gridlines).
• Column K – Show headings (optional): enter Show to show the column/row headings, or Hide to hide the headings.  If not entered, the existing state is not changed (or if a new sheet is added, it is set to show headings).
• Column L – Delete (optional): to delete an existing sheet, enter DELETE.

You can save the file you are using with the Bulk_Sheet_Update sheet as one of up to five templates.  When you save it as a template, you are prompted whether or not to leverage a template any time you run the Bulk Sheet Update macro.  When you leverage one, the contents of the settings sheet (Bulk_Sheet_Update) are copied to the active workbook and the contents are processed (sheet settings applied).  Follow the instructions in cell O1 of the Bulk_Sheet_Update sheet to save a template.