What it does
This macro allows you to take a source file and map it to an output sheet/file, using columns to copy, fixed values to fill in, and formulas to apply. It allows you to specify where to save the output file to, the file prefix, the file type, and the first row to export.

When to use it
When you need to restructure a source data file for using in a report or import file, this can help do so efficiently and consistently.

Why to use it
It allows you to define repetitive steps, saving time and ensuring that the output is valid and consistent, both in structure and location.

Default shortcut
None

Other Details

• Category: Data / Content
• Difficulty: 4/5
• Usage/frequency: 3/5
• Automation factor: 5/5 (estimated 300 seconds saved each time used)
• Type: Bulk
• Date added: 9/22/2022
• Tags: Import, restructure, mapping

Related Macros
None

Other Articles
None

Create Mapped File Example File

Prerequisites
Identify the source data you want to leverage and the output structure you need the data in.

Instructions
Run the Create Mapped File macro. This will create the Create_Mapped_File sheet in the active workbook. Fill in these yellow fields to the right:

• Source Data Sheet Name (required) – this is the name of the sheet that should contain the source data you want to restructure.
• Destination Sheet Name (required) – this sheet will be created (or overwritten) and contain the restructured data.
• Destination Export File Folder Path (required) – this is the folder where the output sheet will be saved to.
• Destination Export File Prefix (required) – this is the primary part of the file name that will be exported. A timestamp will be added to the end of the file name.
• Destination Export File Type (required) – this is the file type that the output sheet will be saved to.
• Destination Export First Row (required) – This is the first row that should be exported (generally this is row 1 to include headers or row 2 to exclude headers).

Then fill in the fields on the left:

• Field Name (required) – the fields in this column will be copied as headers in the destination sheet. For each field, a source column, fixed value, or formula is required.
• Source Column – data in the specified column from the source data sheet will be copied to the destination sheet.
• Fixed Value – the values in this column will be copied directly to the destination sheet.
• Formula – the values in this column will first be appended to the source data (to the column specified in the Source Column field), then values will be copied to the destination sheet. Start the formula with an apostrophe and enter the formula as if you were applying it manually to the source data sheet in row 2. Often lookups to other sheets are needed to help categorize items – include those other sheets in this file.