What it does
Uses a settings sheet (Multi_Sheet_Format) to update several common formats and settings for any selected sheets, whether one sheet or hundreds of sheets are selected.

When to use it
When you have an existing file – generally something you download recurringly – and you want to apply several formatting or other settings to multiple sheets in one reusable bulk step.

Why to use it
It’s an efficient way to apply many common sheet formats/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: 3/5
• Automation factor: 5/5 (estimated 600 seconds saved each time used)
• Type: Bulk
• Date added: 1/1/2014
• Tags: sheets, bulk, format

Related Macros
Sheet Action Picker
Bulk Sheet Update
Bulk Sheet Rename
Bulk Sheet Reorder

Other Articles
None

Multi Sheet Format – Example File

Instructions
After you have identified the sheet(s) you want to update, run the Multi Sheet Format macro.  The first time you run it, it will create a sheet called Multi_Sheet_Format.  This is where you’ll configure the settings you want to apply in bulk.  After filling in any formatting/settings you want to apply, select any sheets you want to apply them to, and run the Muti Sheet Format macro again to apply them.  These are the settings within the Multi_Sheet_Format sheet you can fill in (note that all are optional):

• Freeze panes: enter the cell range where freeze panes should be applied (cell, column, or row).  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.
• Rows to auto-fit: enter the ranges of any rows you want to auto-fit the height for, separating multiple row sets with a comma (i.e. 1:3, 11:13).
• Columns to auto-fit: enter the ranges of any columns you want to auto-fit the width for, separating multiple column sets with a comma (i.e. A:C, H:J).
• Row height: enter the ranges of any rows you want to set the fixed height for, separating multiple row sets with a comma, then an equals sign, then the row height (i.e. 3:12,15:15=6;23:30=8).
• Column width: enter the ranges of any columns you want to set the fixed width for, separating multiple column sets with a comma, then an equals sign, then the column width (i.e. C:F,I:I=14.5,O:Y=3).
• Rows to group: enter the ranges of any rows you want to set the row groups for, separating multiple row sets with a comma (i.e. 3:12,14:23).
• Columns to group: enter the ranges of any columns you want to set the column groups for, separating multiple column sets with a comma (i.e. C:M,O:AD).
• Rows to hide: enter the ranges of any rows you want to hide, separating multiple row sets with a comma (i.e. 3:12,14:23)
• Columns to hide: enter the ranges of any columns you want to hide, separating multiple column sets with a comma (i.e. C:M,O:AD).
• Printed rows to repeat at top: enter the continuous range of rows that should be repeated at the top when the sheet is printed (i.e. 1:3).
• Printed columns to repeat at left: enter the continuous range of columns that should be repeated at the left when the sheet is printed (i.e. A:C).
• Printed row page breaks: enter the row number where the page break should be inserted before, separating multiple row page breaks with a comma (i.e. 36,72,108)
• Printed column page breaks: enter the column letter where the page break should be inserted before, separating multiple column page breaks with a comma (i.e. L,AB,AN).
• Print orientation: enter Portrait or Landscape to set the print orientation.
• Print margins: enter the print margins (in inches) with locations separated by commas in this order: top, right, bottom, left (i.e. 0.3,0.5,0.3,0.5).
• Print scaling/zoom OR fit-to-pages: enter the print zoom percentage as a whole number between 30 to 200 OR enter the fit-to pages as whole numbers for the width and height, separated by an x (i.e. 1×3).
• Table row banding: enter the cell range to use alternating fill colors (often called row banding).
• Variance value formatting: enter the cell range(s) to apply variance formatting (negative numbers as red, positive numbers as green), separating multiple ranges with a comma (i.e. E3:E40,F3:F40,J3:J40,K3:K40).
• Select cell(s): enter the cell range that should be selected by default when users navigate to the sheet.

You can save the file you are using with the Multi_Sheet_Format 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 Multi Sheet Format macro.  When you leverage one, the contents of the settings sheet (Multi_Sheet_Format) are copied to the active workbook and the contents are processed (sheet settings applied).  Follow the instructions in cell K1 of the Multi_Sheet_Format sheet to save a template.