What it does
Uses a settings sheet (Modify_Fields) to apply content, formatting, and other updates to a tabular data across one or more sheets.

When to use it
When you routinely run a report with one or more sheets, and you want to apply the same updates to it – update/add a few fields (values or formulas), apply formatting updates, apply filters, apply freeze panes, etc., this makes it efficient and consistent.

Why to use it
This makes it very easy, efficient, and repeatable to make several common updates to reports you run in a tabular format, across one or multiple sheets.  You can even save your preferences to easily run over and over with just a couple of keystrokes.

Default shortcut
None

Other Details

• Category: Data / Content
• Difficulty: 5/5
• Usage/frequency: 3/5
• Automation factor: 5/5 (estimated 180 seconds saved each time used)
• Type: Bulk
• Date added: 12/28/2017
• Tags: Fields, table, report, update, filter, freeze panes

Related Macros
None

Other Articles
None

Modify Fields Example File

Prerequisites
Open the source data/table you want to modify and identify the updates you want to make.

Instructions
After you have identified the source data you want to modify and the updates you want to make, run the Modify Fields macro.  The first time you run it, it will create a sheet called Modify_Fields.  This is where you’ll configure all the updates you want to apply and the sheets/fields you want to apply them to.  These are the columns within the Modify_Fields sheet you’ll want to fill in:

• Column A – Sheet name (required): enter the name of the sheet containing the field(s) you want to modify/append.
• Column B – Column (required) – enter the column letter that should be modified/appended.  Note that if the field is already populated it could be overwritten based on the values in other fields on the settings sheet.
• Column C – Header Row (required) – enter the row number that contains headers (usually this is row 1).  It is expected that data starts in the next row.
• Column D – Header Label (required): enter a brief, descriptive header/label for the column.
• Column E – Formula (optional): if desired, enter the formula to apply to the column, as if it was being manually entered to the first non-header row on the sheet.  If referencing other files, make sure the full file path is included (not just the file name), otherwise the formula will show an error when the external file is not open.
• Column F – Formatting (optional): if desired, apply any formatting you want to apply to the sheet/column in this column.
• Column G – AutoFilter (optional): enter No to not apply AutoFilter to the data set.  Otherwise it will be applied.
• Column H – Freeze Panes (optional): if desired, enter the range where you want freeze panes applied (i.e. B2, B:B, 2:2, etc.).  
• Column I – Bold Header (optional): enter No to not bold the header label.  Otherwise it will be bolded.
• Column J – Column Width (optional): if desired, enter the width of the column (number between 1 to 200) or enter A to auto-fit the width of the column.
• Column K – Hide Column (optional): enter Hide to hide the column from view if you want to hide the column.
• Column L – Filter Values (optional): to filter the data set in the column, enter one or more values to filter, with multiple values separated by a semi-colon.

Note – as with most settings-sheet based macros, you can set up to five files as defaults that can be leveraged over and over again.  When you run the Modify Fields macro, if there are any saved default files, you will be prompted to use any, if desired.  The workbook should contain a very similar structure (sheet names, range names, etc.).  When applying a default, the settings sheet is copied then processed.  If the workbook structure is not similar, this could result in errors.  To save defaults for this macro, follow the instructions in the cell comment in cell P1.