This is the help page for the Format Copier Utility for Excel
Open the format copier workbook and enable macros.
The tool copies the page layout and formatting options from worksheets in this workbook (Templates) and applies them to worksheets in the selected workbooks (workbooks to be formatted).
You can keep formatted worksheets in this workbook to use as templates for formatting in the future. And manage the default formatting options for each template in the Template List. More on that later…
- Open a worksheet that contains the formatting that you want to apply to other worksheets. Copy the worksheet into the end of this workbook, after all existing sheets. You can make changes to the formatted sheet after it is copied in. Note: The formatted sheet is now stored in this workbook and can be used for formatting in the future.
- Open the workbook(s) to be formatted and launch the Format Copier.
Note: Workbooks to be formatted must be opened in the same instance of Excel as the Format Copier workbook for the formatting tool to recognize them. Press Ctrl-Open from this file to open workbooks in the same instance of Excel .
- Follow the steps on the Format Copier window (see below) and click Run to format reports.
Note: Hidden worksheets will not be formatted. Protected sheets must be unprotected before formatting.
The Template List is a list of all templates stored on the Format Copier sheet in this workbook. The purpose is to help keep your templates organized. You can add a description for each template. Descriptions can be used to: specify workbooks/reports to apply formatting to, input how often it is used, list characteristics of the template that differ from others, or add anything you want to remember when applying formatting.
The Default Formatting Options section stores the default formatting options for each Template. This way you don't have to remember which formatting options you use for each Template. Select Y or N from the in-cell dropdown under each option. When you select the Template Sheet in the Format Copier (step 1 on above screenshot), the checkboxes below will automatically be selected based on your default options.
Template Names are automatically added to the list when new Templates are added to the workbook.
If your templates contain pictures in the header/footer you will need to add the picture file locations in the Header/Footer Picture File Paths columns. This will allow the pictures to be copied to the workbooks to be formatted. Adding the file locations is easy:
- Select the cell of the header/footer area that contains a picture.
- Click the Add File Path button.
- Browse for the picture and click open.
The file location will be inserted in the selected cell and a preview of the image will be inserted in the cells comment. The image in the cell comment is for you to preview, and is NOT the image that is copied to your formatted worksheets.
- Page Layout Options for Printing
- Header and Footer including images, pictures, or logos
- Margins, Page Orientation and Scaling, Page Size
- Print Area, Page Breaks, Print Titles, Print Options
- Page Breaks for Printing
- Cell Formatting & Conditional Formatting
- Font, Alignment, Number, Styles, Borders, Fill, Protection
- Outlines or Row & Column Groups
- Freeze Panes
- Hidden Rows & Columns
- Column Widths
- Row Heights
Contact us with questions, comments, ideas for improvement, or additional services.