Important Files and Macros

Promotion Macro

The Promotion Macro turns a vendor’s Navy Deal Offer Form (DOF) into a ready-to-key START promotion template in one click. When you click Export Promotion it:

1. Builds the START promotion template rows (item, zone, fixed price, retail, comments).
2. Logs the promotion to the brand’s tab in the Buyer Promo Tracker and checks for overlapping promotions.
3. Optionally loads the items into the Fact Tags workbook for sale signs.
4. Saves the template to the shared promotions folder, split into one file per month for long promotions.
5. Drafts the Outlook email to the right team with the file attached, ready for you to review and send.

Promotion Macro (2026)

Buyer Promo Tracker 2026

Before You Run It

• Brand tab exists in the tracker. The tracker must have a tab named exactly like the vendor/brand name you enter in the macro (e.g. MSI). See Brand Tabbing.
• Paths are set. The START template, tracker and Fact Tags file locations set in the macro point to files you can open.
• Outlook is installed and signed in, so the macro can draft the email.
• Close the tracker on other computers if possible. The macro saves it, and a copy someone else has open will be read-only.

Step 1: Fill In the Deal Offer Form

Paste or type the vendor’s DOF into the macro workbook. The yellow cells are inputs; the red boxes next to the arrows are the cells the macro reads for the brand, your ID and the buyer number.

Item rows start on row 30 (one item per row, up to row 333):

Step 2: Click Export Promotion and Answer the Two Questions

The macro then runs on its own. Here’s what happens, in order:

1. Builds the template. Opens the START promotion template and fills one row per item.
2. Logs to the tracker. Opens the Buyer Promo Tracker, finds the tab named after the brand, adds the rows under the last entry with the promo dates, scanback $ and the Sent to START answer, then saves the tracker.
3. Checks for overlaps. The tracker’s Overlap Check column (C) flags any item already on promotion for those dates. If any row says Overlap, you’ll see “Overlap Detected! Check Tracker.” and the macro stops: no file is saved and no email is drafted.
4. Loads Fact Tags (if you said Yes).
5. Saves the file(s). The file is named m.d.yy-m.d.yy Brand Month Promotion.xlsx, e.g. 3.1.26-3.31.26 MSI March Promotion.xlsx. Missing brand/month folders are created automatically.
6. Drafts the email (if sending now) and shows “Process Complete.”

Step 3: Review and Send the Email

When you choose to send now, Outlook opens a draft for each file. The macro picks the recipient from how soon the promotion starts:

1. Open the attached file and verify the START template: items, fixed prices, dates, buyer ID and submitted-by ID.
   *The template always applies “Both Regular and Clearance”.
2. Click Send. The email is only drafted, never sent automatically.

Step 4: Check the Tracker

Each promotion is logged to the brand’s tab in the Buyer Promo Tracker, which the macro saves automatically:

Troubleshooting

Promotion Macro w/ automatic tracking and filing

Promotion Macro

Buyer Promo Tracker

Buyer Promo Tracker Template

1. Save to path team can access

Sales Update Button

Units Sold for specified period for all locations will populate into column AG. Trailing credit will auto calculate in column AH

Brand Tabbing

1. Duplicate Brand tab for each brand and rename the tab for each brand.

*if Brand Tab Name does not match Brand name input on Promotion Macro the macro will be unable to copy information over to Promo tracker. (Text just needs to match)

Assign Tracker Path to Promotion Macro

3. Go to path Share Path where YOUR Tracker is located.
4. Right click, select Copy as path

Back onto Promotion Macro Workbook.

5. Right click macro button
6. Click "assign macro"
7. Click "VendorDOF" and select Edit
   Assign Macro                                  ? ✕

   Macro name:

   CopyData
   PERSONAL.XLSB!FFINSPECT
   VendorDOF

   Macros in: All Open Workbooks

   Description

   Edit Record...

   OK Cancel
8. Under "Configuration" paste the path of your Buyer Promo Tracker into TrackerPath: in between the quotations.

' --- CONFIGURATION ---

TemplatePath = "\\server\share\folder"

TrackerPath = ""

FactMasterPath = "X:\shared\folder"

9. Under "Part 4: File Generation Loop" and "Revised Dynamic Paths" provide a path for START templates to save to. For Now location & Later location

' --- REVISED DYNAMIC PATHS ---

If SendNowResponse = vbYes Then

' Save to: ...\2026\[Brand Name]\[Month]\

SaveFolderString = "\\server\share\folder" & BrandName & "\" & PromoMonth & "\"

Else

' Save to: ...\2026\Send Later\[Month]\[Brand Name]\

SaveFolderString = "\\server\share\folder" & PromoMonth & "\" & BrandName & "\"

End If

10. Do Ctrl + S to save and close the window
11. *You must reassign macro
12. Right click macro button again and click assign macro.
13. Click VendorDOF and click OK
14. Save document and close.

*Now, when you run the promotion macro. The promotion information will be logged to YOUR teams Tracker file.

Fact Tags

The Fact Tags V4 workbook holds every fact tag the team has built (the shelf signs for out-of-box, on-display items) and creates the import files the sign team uses to print them. Each tab has a job:

Regular Fact Tags (Category Tabs)

Regular fact tags are the everyday shelf signs for items on display. Each category (Apple, Audio, Cameras, Computers, TV-Video, etc.) has its own tab where its tags are kept, plus an Email Fact Tags button that sends that category’s tags to the sign team. The Master tab combines all the categories so the SALE MASTER can find any item by RIN.

The workflow is: add the tag on its category tab → click Email Fact Tags → click Update Master.

Regular Tags Step 1: Add the Tag to Its Category Tab

Go to the category tab, find the first empty row below the header (row 5; tags start on row 6) and fill in one row per item:

Example (from the Apple tab):

Regular Tags Step 2: Click Email Fact Tags

On the category tab, click Email Fact Tags. The macro builds two files for the sign team and drafts the email:

1. Open both attachments and check them: the New file should contain only the tags you just added.
2. Click Send. The sign team prints from the New file and keeps the Master file as the category’s full, current list.

Every category tab works the same way; only the tab name, export folder and file names change (e.g. Audio Master Fact Tag…, TV-Video New Fact Tag…). Running it again on the same day replaces that day’s files.

Regular Tags Step 3: Click Update Master

After adding or changing tags, go to the Master tab and click Update Master (the same button is on Clearance Master). Do this before building sale signs, or new items will show #N/A on the SALE MASTER.

Regular Tags Troubleshooting

Sale Fact Tags (SALE MASTER)

The SALE MASTER tab turns a list of RINs and sale prices into a sale-sign file for the sign team. You load the items, check that every item has a fact tag, then click EXPORT TO SIGN TEAM. The macro builds the sign-team file, removes items with no fact tag, names the file the way the sign team requires, and drafts the email.

Step 1: Load the Items

Automatically: run the Promotion Macro and answer Yes to “Do you want to generate sale tags?”. It writes the RINs, regular and promo prices, and dates into the SALE MASTER for you.

Manually: paste RINs into ItemID (D), starting on row 2 with no blank rows, then fill in the dates (H–I) and prices (L–M).

Step 2: Check for #N/A

After the RINs are in, the lookup columns fill in automatically. A row showing #N/A means that item has no fact tag on the Master tab. For each one, either:

• Build the fact tag: add the item to its category tab, following the sign team’s rules on the Instructions tab (Name up to 105 characters, Description up to 250, // for a line break, no leading or trailing spaces). Then click Update Master so the SALE MASTER can find it.
• Skip it: if the item doesn’t need a sign, leave it. The export now removes #N/A rows automatically, so no error rows reach the sign team.

Step 3: Click EXPORT TO SIGN TEAM and Answer the Prompts

Stay on the SALE MASTER tab (the macro reads whichever tab is active) and click EXPORT TO SIGN TEAM. Answer three prompts:

1. Department/Brand, e.g. Apple. Used in the file name, subject and email.
   Department Name                       ✕

   Enter the Department (e.g., Electronics):

   OK Cancel
2. Start and end date in MM/DD/YY format. These set the week number, file name and subject; the dates printed on the signs come from columns H–I.
   Start Date                                 ✕

   Enter Promotion Start Date (MM/DD/YY):

   OK Cancel

   End Date                                   ✕

   Enter Promotion End Date (MM/DD/YY):

   OK Cancel

Then the macro runs on its own:

Step 4: Review and Hold the Email

Outlook opens a draft to the sign team with the file attached. The file name and subject follow the sign team’s format:

1. Open the attachment and verify the items, prices and dates.
2. Hold the email until START has keyed/approved the promotion, then send it.
   *START DOES NOT WANT SIGNS SENT UNTIL THE PROMOTION HAS BEEN KEYED/APPROVED.
3. The attachment lives in your Windows temp folder, so keep the draft open (or save the attachment) until you send it.

Troubleshooting

Ad Planner & DB Uploader

The NEX Ad Planner workbook covers an ad from start to finish:

1. Measure past ads. Import QuantaSense sales data and calculate how much each item’s sales rose (its lift) in the weeks it was advertised.
2. Plan the next ad. Build the week’s item list, pull item details from RMS, and export the lineup to the marketing PowerPoint template.
3. Upload to the Ad Database. The export also fills the Ad Database Import sheet, which you send to the Ad Database with the Update Ad Database form (Ctrl+A).

NEX Ad Planner (v22)

Before You Start

• Enable macros when you open the workbook, and always work in the .xlsm file.
• PowerPoint templates: put AE_and_FE_Templates.pptx, Email_Templates.pptx and Large_Event_Template.pptx in the same folder as the workbook. The export looks for them there.
• Network access to the Ad Database (an Access file on the shared drive) and to RMS. RMS is read through the Oracle ODBC driver with a read-only login; the password is read from a protected network file, so you never type it.
• Installed software: Excel, PowerPoint and Outlook, the Microsoft Access Database Engine (ACE OLEDB) and the Microsoft ODBC for Oracle driver.
• VBA reference: in the VBA editor (Alt+F11) under Tools → References, make sure Microsoft ActiveX Data Objects 6.1 Library (or the latest version) is ticked. The DB Uploader needs it.
• The DB Uploader keeps a copy of the department lookup list in C:\temp; it creates the folder if it doesn’t exist.

The Weekly Workflow at a Glance

How the Ad Planner Works

The workbook links your QuantaSense sales data, RMS item data, the PowerPoint ad templates and the Ad Database. These are the tabs you work in:

How lift is measured

Baseline = the item’s normal, un-promoted sales. For each item, the Baseline Avg/Wk is the average weekly units it sold over the last 26 weeks excluding ad weeks. That answers “what does this item sell when we don’t advertise it?”, which is the right comparison, rather than a calendar-year average.

• Unit Lift = ad week units − baseline: the extra units the ad drove.
• Lift % = unit lift ÷ baseline: the headline number for one item.
• Weighted Lift % (Lift Query) = total unit lift ÷ total baseline units across a group of items.

Ad Week Calendar

A running list of every ad you run. Add a row each time:

Data Import (and Build Ad Summary)

Holds the QuantaSense sales data the analysis runs on. You don’t paste into it; the Import Data button loads it for you.

1. Pull the data. In QuantaSense, export the Ad Planner V1 layout for your category group covering the trailing 26 complete weeks with the LC26W metrics selected. Include the ad week(s) you want to measure and the normal weeks around them. Save it as .xlsx.

2. Click Import Data and pick the file. The macro:

1. Opens it read-only and checks the layout: row 3 must have Group in A, SKU in E, Week in G and Inv OH in M. Otherwise you’ll see “File does not match QuantaSense V1 layout.”
2. Opens a temporary Select Group(s) to Import sheet with a checkbox for every group in the file (all ticked). Untick any you don’t need, then click Confirm Selection (or Cancel).
3. Clears the old data and writes the selected rows from row 5, then tells you how many rows were imported and how long it took.

3. Click Build Ad Summary. A temporary Select Ad Week(s) to Analyze sheet lists every week in the data. Tick the ad week(s) you want to measure and click Confirm Selection. Results go to the Ad Summary tab.

Ad Summary

One row per Transaction RIN per selected ad week, showing how much the ad lifted sales compared with a normal week. The header shows the weeks analyzed (E3), the item count (H3) and when it last ran (K3).

Using it: filter Verdict to STRONG/MODERATE, sort Lift % from largest to smallest to rank the best performers, or filter Group/Dept/Class/Sub-Class to focus on a category.

Predecessor lookup: to see how an older, similar item did in every ad week in the data, type its Parent RIN in M3 and run View → Macros → LookupPredecessor. The results appear below the main table.

Lift Query

Rolls up the Ad Summary for a vendor, product or category: the number to quote when discussing performance with buyers or marketing. Fill in any of the inputs (blank = no filter) and click Update:

Results: SKUs matched, total ad units and revenue, total unit and revenue lift, and the Weighted Lift % (total unit lift ÷ total baseline units), followed by the matching rows sorted by Lift %. The weighted figure counts every unit equally, so it isn’t skewed by small items the way a simple average of Lift % would be.

Ad Week Planner

Where you build the ad. The header fields come first:

Then one item per row from row 10 (up to row 89):

The formula columns (Savings, the LY columns, Elasticity and Brand) are locked so the formulas can’t be deleted; don’t type into them. If a brand isn’t detected, add it to the workbook’s BrandList tab.

Import SKU Data (the UpdateButton macro): select the Trans RIN cells to update in column A, or click any cell outside column A to update every row from 10 down, then click the button. For each RIN it reads the item’s details from RMS and the newest Ad Information from the Ad Database, then shows which rows were updated.

Export To PowerPoint

When the INCLUDE? column is set, click Export To PowerPoint on the Planner and confirm the prompt. The macro:

1. Checks the setup. Ad Week # must be filled, U9 must say Brand, and at least one item must be marked YES.
2. Picks the template from the Vehicle (below) and asks you for a layout if there’s a choice. If more items are marked YES than the layout has slots, it stops and tells you.
3. Asks where to save the PowerPoint (suggested name e.g. 25_AE_FE_20261002.pptx). Cancel here and nothing else runs.
4. Bulk loads the Ad Database Import sheet with every item row on the Planner (see Ad Database Import).
5. Builds the slide. Each slot gets the description, VPN, RIN, retail, promo price, savings and call out, then the file is saved and PowerPoint closed.
6. Logs the export to the Ad Tracker and shows a summary with the saved file’s location.

Ad Database Import (and the DB Uploader)

The rows that go into the Ad Database. Export To PowerPoint adds one row per Planner item below any existing rows:

Fill in anything still blank that the ad needs (Page, MSRP, Call Outs, Grouping Info, Coop $, Order Frequency, etc.).

Upload to the Ad Database (Ctrl+A)

The uploader finds each field by its header text in row 4, so columns can move but headers must keep their exact names. Don’t merge cells in rows 1 or 4.

1. On the Ad Database Import sheet, select one cell in each row you want to upload, all from the same department (up to 500). Rows with no Wk and no RIN are skipped.
2. Press Ctrl+A to open the Ad Items form (shown below). It copies the latest lookup list to C:\temp and fills in what it can; complete the rest.
3. Click Update Ad Database. Each selected row becomes a new Ad Database record with the form’s week, event, department and ad dates plus the row’s values.
4. When “Done!” appears, open the Ad Database and check the new records. You may still need to add imaging, Parent RIN, RPC dates or other details by hand.

Troubleshooting

Ad Bible (Standalone)

The Ad Bible is a stand-alone copy of the Ad Planner’s Ad Database Import sheet and its DB Uploader. Use it to key items straight into the Ad Database without the planner: no QuantaSense data, lift analysis or PowerPoint export. It’s handy for vendor submissions, last-minute additions, or anyone who only needs to enter ad items.

1. Type the RINs.
2. Click Update Information to fill in item details from RMS and past ad copy from the Ad Database.
3. Finish the ad details and upload the rows with Ctrl+A.

Ad Bible (Standalone)

Ad Bible vs. Ad Planner

Before You Start

• Enable macros when you open the workbook.
• Network access to the Ad Database on the shared drive and to RMS. RMS is read through the Oracle ODBC driver with a read-only login; the password is read from a protected network file.
• Installed: the Microsoft Access Database Engine (ACE OLEDB) and Microsoft ODBC for Oracle driver. In the VBA editor under Tools → References, Microsoft ActiveX Data Objects 6.1 Library must be ticked.
• The upload form copies the department lookup list to C:\temp (created if missing).

The Ad Database Import Sheet

One row per ad item from row 6. The column headers are in row 5, grouped under Week, Item Info, Marketing, Pricing, Image, Order Info and Coop. New Item and Online Exclusive default to N, Change Type to FIXED, Pricing Strategy to YFB and Year to 2026.

Step 1: Enter the Items

For each item, type the Wk (A), e.g. 25AE, and the RIN (D), one per row, starting on row 6. RINs need at least 6 digits to be looked up.

Step 2: Click Update Information

1. Select the RIN cells in column D that you want filled in. Only cells in the RIN column are processed, and the header row is skipped. If nothing in column D is selected you’ll see “Please select the cells containing the RIN numbers…”.
2. Click Update Information. For each selected RIN the macro reads RMS and the Ad Database and fills the row:

Step 3: Finish the Ad Details

Step 4: Upload to the Ad Database (Ctrl+A)

1. Select one cell in each row to upload, all from the same department (500 at most). Rows with no Wk and no RIN are skipped.
2. Press Ctrl+A to open the Ad Items form (shown below) and fill it in.
3. Click Update Ad Database. Each selected row becomes a new record.
4. At “Done!”, open the Ad Database and check the new records; you may still need to add imaging, RPC dates or other details by hand.

Troubleshooting

Induction Checker (Item Validation Tool)

The Induction Checker is an Excel pre-check for RMS item induction workbooks, the templates used to create new item families, add GTINs and sizes, and attach suppliers in Oracle RMS. One run reviews every row on the Item tab and the Location tab, and catches the mistakes that would otherwise get an induction rejected or need fixing after it’s loaded:

• Missing or invalid data: required fields for each Action, allowed dropdown values, number formats, GTIN length and check digit, prohibited characters.
• Rules between fields: Case Size must be a multiple of Inner Size, one primary supplier per item, one Case/Each/Inner row per GTIN, department-specific requirements.
• Live RMS lookups: whether the supplier, Dept/Class/Subclass, size, UDA values or GTIN already exist, and whether a new child item matches its parent’s hierarchy and retail.
• Description quality: brand at the start, proper case, spelling, size and colour words that don’t belong, the 100-character limit.

Every problem cell is colour-highlighted, many get a comment explaining the exact fix, and a pass/fail stamp with your user name and the time is written to cells A2–A4, so a reviewer can see the file was checked before it was submitted.

Induction Checker workbook

Item Validation Tool User Guide v2.0

Where It Fits in the Induction

1. Build the induction workbook as usual: one row per item on the Item tab (headers on row 7, data from row 8), with the Action for each row, and the Location tab if the Action needs it.
2. Run the Induction Checker on it.
3. Fix everything highlighted and run it again. Repeat until cell A3 says Successful, No Errors were Found.
4. Save and submit the workbook for RMS processing. The A2–A4 stamp shows who checked it and when.

The checker understands the four induction Actions and applies different rules to each:

Before You Start

• Enable macros when you open the checker workbook.
• Network or VPN access to RMS. RMS is read through the Microsoft ODBC for Oracle driver with a read-only login; the password is read from a protected network file, so no one types it in.
• Access to the approved brand list on the shared induction drive. If it can’t be opened, only the brand-name check is skipped.
• Keep the template’s header names intact. The checker finds columns by header text, not by column letter, so inserting or moving columns is fine but renaming a header is not.

How to Run It

1. Open the induction workbook you want to check.
2. Open HighlightSizeColorDuplicates2.xlsm and enable macros. Its window hides itself; that’s expected. It stays open in the background so its macro can run on any workbook.
3. Click into the induction workbook, ideally on the Item tab. If you’re on the Location tab, the checker finds the Item tab itself (the sheet with an Action header on row 7).
4. Press Alt+F8, choose HighlightSizeColorDuplicates2 and click Run.
5. Wait for the Check complete! box. It either says No errors found or lists each error type with how many were found.
6. Review the highlighted cells, fix them, and run it again.

Reading the Results

The highlight colour tells you what kind of problem it is:

What It Checks

The checker reports about 60 error types. The full list, with what each means and how to fix it, is in the user guide; this is the overview:

Use Case Examples

Recommended Fix Order

1. Start with the summary in A4 to see which error types are present.
2. Fix orange required-field and format problems first; they often cause other errors.
3. Read the comments for errors that give a specific RMS value, UDA, Phase ID end date or description detail.
4. On Add Tran Item or GTIN rows, fix hierarchy mismatches (purple) before looking at RMS duplicate results on the same row.
5. Resolve RMS existence (pink) and size (yellow) conflicts.
6. Check duplicates across the whole sheet, including hidden or filtered rows.
7. Run the checker again until A3 says Successful, then save.

Built-in Exceptions

• Supplier Color may be blank when the NRF Color Code is 000.
• Package Type, Depth, Width, Height, Gross Weight and Presentation Method are only required for departments that need dimensions. Dept 884 Class 1401, and buyers 60, 90, 720 and 960, are exempt.
• The brand-at-start check is skipped when S/O appears in the description, and accepts the brand without apostrophes.
• Colour code 262S is never flagged as an already-built colour + size.
• Duplicate GTINs are only compared within the same Action.
• Perishable departments accept Yes or No; only a blank is an error.
• The Location tab is only required when an Action on the Item tab needs it (not for Add Tran Item or GTIN or Add Item Supplier).

Keeping It Up to Date

Department lists, allowed values, season and Phase IDs, buyer exemptions and required UDAs are written into the VBA. When the business rules change, update the macro and the user guide together. In particular, each new season needs adding in two places: the allowed Phase ID list and the season end-date table (e.g. FAL26 ends 10/31/2026, WNT26 ends 01/31/2027), otherwise the new Phase ID passes without being checked for expiry.

Troubleshooting

Pack Item Induction Checker

The Pack Item Induction Checker validates the Pack Item Induction Template, the workbook used to set up packs in Oracle RMS: a sellable or orderable unit made of other items (components), such as a multi-pack, a gift set or a vendor case pack. A pack induction is spread across three linked tabs, so mistakes are easy to make and hard to spot by eye. One run of the checker:

• Checks every row on the Pack Item, Component Item and Location tabs together, including that each pack appears on all three.
• Confirms required fields for each Action, dropdown values, number formats, GTIN length and check digit, Pack Family ID rules and description rules.
• Looks up RMS to confirm suppliers are active, Dept/Class/Subclass exist, new GTINs aren’t already used, and component items exist.
• Fixes two things automatically: it swaps component GTINs for their RMS item number (TransactionRIN), and stores Location-tab pack numbers as text.
• Colours every problem cell, adds a note explaining it, and stamps the result in A2–A4 of the Pack Item tab.

The checker is built into the template (macro ValidatePackTemplate), so there’s nothing extra to install.

Pack Item Induction Template (with checker)

Pack Checker vs. Induction Checker

The Template’s Tabs

The checker finds columns by header name, so columns can be moved, but headers mustn’t be renamed. The template’s own Populate Template button is a separate step that builds the hidden upload tabs; run the checker first.

Actions and Required Fields

Rows count as data only when Action, Pack Family ID, Pack Number, Description, GTIN or Supplier Site is filled, so the template’s pre-filled defaults on empty rows are ignored. Perishable is also required for perishable departments (e.g. 551, 590, 884, 991).

How to Run It

1. Open the template and enable macros. If the window doesn’t appear, go to View → Unhide and pick the template; its open macro hides the window.
2. Fill in the Pack Item, Component Item and Location tabs.
3. Press Alt+F8, choose ValidatePackTemplate and click Run. The status bar shows progress (connecting to RMS, checking GTINs, checking rows).
4. Read the Pack Template Check summary: the total number of issues and a count per category, any component GTINs that were replaced, and any notes such as RMS checks being skipped.
5. Fix the yellow and red cells (hover to read each note), then run it again. Earlier highlights and notes are cleared automatically each run.
6. When A3 shows Successful, No Errors were Found, save the file and continue with Populate Template and submission.

Reading the Results

Checker notes start with CHK:. Only those notes and their colours are cleared on the next run; your own comments are left alone.

What It Checks

Automatic Fixes

• Component Item GTIN → TransactionRIN. The Component Item column can hold a transaction item number or a GTIN. If you enter a GTIN, the checker looks it up in RMS and replaces it with the item’s TransactionRIN, leaving an information note with the original GTIN. If it isn’t found, the cell turns red.
• Location Pack Number stored as text. Long numbers can lose leading zeros or turn into scientific notation. The checker converts the Location tab’s Pack Number column to text (the same as Data → Text to Columns → Text).

Use Case Examples

Keeping It Up to Date

The allowed values, season and phase lists, perishable departments, required fields and country codes are lists at the top of the macro, so they’re quick to edit in the VBA editor (Alt+F11, Module2). Add new Season and Phase IDs as they’re released. The Add GTIN required-field list is marked in the code as an assumption; confirm it with the induction team.

Troubleshooting

Sort and Format BI Report

Reports exported from the BI tool arrive with five rows of title and filter information above the column headers, unsorted rows, and columns too narrow to read. Sort and Format BI Report cleans one up in a single click:

1. Sorts the whole report by PARENT, then TRANSACTION, so every parent’s items sit together in order.
2. Highlights the PARENT and TRANSACTION values in soft yellow so the item numbers stand out.
3. Auto-fits every column and row so all the text is visible.
4. Deletes rows 1–5 (the report title and filters), leaving the headers in row 1, ready to filter, pivot or paste into another workbook.

It works on whichever sheet you’re looking at, so it’s meant to live on a Quick Access Toolbar button and be used on any report you open.

Sort and Format BI Report workbook

Before and After

Before: the Item Induction Results Report w/UPC & VPN as exported. Rows 1–5 hold the report title and the selection criteria (buyer, user, department and create dates), the column headers are on row 6, and the columns are too narrow to read the vendor, parent and item names.

Click a screenshot to open it full size.

After: the title and criteria rows are gone, so the headers are on row 1. Rows are sorted by PARENT and then TRANSACTION, so each parent’s sizes run in order (6, 6.5, 7, 7.5…). The PARENT and TRANSACTION values are highlighted yellow and the columns are widened to fit, ready to check UPCs, VPNs and induction status.

One-Time Setup: Add the Button

1. Save CustomSortandFormat.xlsm somewhere permanent, then open it and enable macros. Its window hides itself, which is expected: it stays open in the background so the macro can run on other workbooks.
2. In Excel go to File → Options → Quick Access Toolbar.
3. Under Choose commands from, pick Macros, select CustomSortandFormat.xlsm!CustomSortAndFormat and click Add >>.
4. Click Modify… to choose an icon and a display name such as Sort BI Report, then OK.

How to Use It

1. Export the report from BI and open it in Excel.
2. Make sure you’re on the report’s sheet and the headers are on row 6, with columns named PARENT and TRANSACTION (any position, any capitalisation).
3. Click the Sort BI Report button.
4. When “Sort, targeted highlight, autofit, and row cleanup completed!” appears, the report is ready. Save it.

How It Works

Troubleshooting

QTY KEY Macro

The QTY KEY Macro (LocationLookup) fills in the Location tab of an induction workbook from RMS. For every row with an Item and a Location, it looks up that item at that store in RMS and writes:

• Primary Supplier: the supplier RMS has set up for the item at that location.
• Location – Store Order Multiple (the quantity key): how the store orders the item, as Cases, Inner or Eaches.

Typing these by hand means looking up every item/store pair, which is slow and easy to get wrong on large rangings. The macro reads all the rows at once and queries RMS in batches of 500 items, so even thousands of rows fill in within seconds. Rows RMS has no record for are left exactly as they are.

QTY KEY Macro (LocationLookup) workbook

Example

Location tab before running (Item and Location entered; supplier and quantity key blank or out of date):

After running (filled from RMS shown in green; row 4 had no RMS record for that item and store, so it was left alone):

Note that 0820 matched store 820 (leading zeros are ignored), and that row 3’s Eaches was replaced with the value RMS holds.

One-Time Setup

1. Save LocationLookup.xlsm somewhere permanent, open it and enable macros. Its window hides itself, which is expected: it stays open in the background so the macro can run on your induction workbook.
2. Add a button: File → Options → Quick Access Toolbar, choose commands from Macros, pick LocationLookup.xlsm!LocationLookup, click Add >>, then Modify… to name it e.g. QTY KEY.
3. Optional: save the workbook in %APPDATA%\Microsoft\Excel\XLSTART so it loads hidden every time Excel starts.

How to Use It

1. Open the induction workbook and fill the Location tab’s Item and Location columns.
2. With that workbook active (any tab), click the QTY KEY button (or Alt+F8 → LocationLookup → Run).
3. The macro runs silently: there’s no “done” message. When the cursor returns, check that the Primary Supplier and Store Order Multiple columns have filled in.
4. Fill any rows that stayed blank by hand. Those item/store pairs aren’t set up in RMS yet (e.g. a new store for that item).
5. Run your induction checker and submit.

How It Works

Troubleshooting

AGC Macro

The AGC Macro turns a greeting-card vendor’s item list (American Greetings and Papyrus-Recycled Greetings) into ready-made Add Tran Item or GTIN rows on the item induction template. Card lines arrive as long lists of card numbers and UPCs; keying each one into the template, looking up its parent and transaction item, retail and supplier, and fixing the UPC format by hand takes hours. In one click the macro:

1. Reads the vendor list (card number and UPC) from the sheet you’re on.
2. Opens the shared item induction template and adds one row per card below any existing rows.
3. Cleans each UPC into a 12-digit GTIN and fills the fixed card-line values (Action, Dept/Class/Subclass, colour, size, buyer).
4. Looks up each card number in the TPGC Master database (Microsoft Access) for its Parent Item, Transaction Item, Initial Retail and supplier.
5. Sets the supplier from the card-number prefix: AGCE… → American Greetings, PRGS… → Papyrus-Recycled Greetings.

AGC Macro workbook

Before You Start

• Enable macros when you open the workbook.
• Network access to the shared drives holding the induction template (109) and the TPGC Master Access database. Both locations are set in the macro.
• The Microsoft Access Database Engine (ACE OLEDB 12.0) installed, so Excel can read the Access database.
• A vendor list with headers in row 1, including cpn_nbr (the card number) and prim_upc_cd (the primary UPC). Other columns can be there too; they’re ignored.

How to Use It

1. Open AGC_Macro.xlsm and paste the vendor’s list onto Sheet1, starting at A1 so the headers are in row 1. Clear any rows from the previous run first.
2. Stay on that sheet and click the button on it (or Alt+F8 → CopyData_And_AccessLookup_Optimized → Run).
3. The induction template opens and fills in. When “Fast processing complete! (n rows added)” appears, switch to the template.
4. Review the new rows: any card without a database match has a blank Parent Item, Transaction Item and Initial Retail to fill in.
5. Use File → Save As to save the template under a new name for this induction. Don’t save over the shared template.
6. Run the Induction Checker on it, fix anything flagged, and submit.

What Gets Filled In

Text columns (Dept, Class, Subclass, description, colour, size, GTIN, item numbers, supplier, buyer) are formatted as text first, so leading zeros such as 000 and 0000 are kept.

How UPCs Are Cleaned

Vendor files mix UPC formats. The macro strips anything that isn’t a digit, then makes it a 12-digit UPC-A GTIN:

Example

Vendor list on Sheet1:

Rows added to the template (Parent, Transaction and Retail from the database):

Troubleshooting

Email Count

Email Count measures the Data Steward Team’s workload straight from Outlook. One click scans the team’s shared mailbox, picks out the item induction and maintenance requests, and builds a PivotTable of request emails per day. Use it for weekly workload reports, to spot spikes around seasonal or ad deadlines, or to back up staffing and turnaround-time discussions with real numbers instead of estimates.

• Reads the Data Steward Team mailbox only, never your personal inbox.
• Counts mail in the team’s working folders: Inbox, each team member’s personal folder, Item Induction and Item Maintenance.
• Keeps only emails whose subject looks like a request (e.g. Item Induction, Add Supplier, GTIN Delete) and drops confirmations such as Successful Upload.
• Writes every matching email to a RawData sheet and summarises it on PivotResult.

Email Count workbook

How to Use It

1. Make sure Outlook (desktop) is open and the Data Steward Team shared mailbox appears in your folder pane.
2. Open ExportEmailToPivot.xlsm and enable macros.
3. On Sheet1, click Update Pivot Table. It can take 1–2 minutes on a full mailbox; Excel looks frozen while Outlook is read.
4. If a folder can’t be found you’ll get a list of the ones skipped; the rest are still counted.
5. When “Done. n matching emails processed into the pivot table.” appears, you’re taken to PivotResult.

What Gets Counted

Request keywords:

Reading the Results

PivotResult shows one row per day and the total for the period (illustrative numbers):

RawData holds the detail behind it, one row per email: ReceivedDate, Folder and Subject, as an Excel table named EmailData.

Things to Keep in Mind

• Every email counts, including replies. A request with three RE: replies in the same folder counts four times. Treat the number as email volume, not unique requests.
• Keywords match inside words. Short keywords such as AIS, MIM or NIB also match words that contain them, and COMPLETE also excludes subjects with Completed or Incomplete. Spot-check RawData if a number looks off.
• Only what’s still in the folders. Emails moved elsewhere, archived or deleted aren’t counted, and read Inbox mail is skipped.
• One folder per name. If the mailbox has two folders with the same name, only the first one found is counted.
• Adding a team member or a new keyword means editing the folder and keyword lists in the macro (Alt+F11, Module1).

Troubleshooting

TPGC Macro

The TPGC Macro sets up third-party gift cards (TPGC) for the point-of-sale system. A gift card needs records in up to five POS tables: the UPC cross-reference, the non-merchandise item, its price or price range, and any activation fees attached to it. Each table has its own layout and a dozen fixed codes. Buyers fill in one simple row per card on the TPGC Cheat Sheet; one click then:

1. Checks every row and skips any with missing or invalid data, so the tables never get out of step.
2. Writes each card to the right tabs of the MNT Master Creator workbook, based on its Type of Gift Card, adding all the fixed codes for you.
3. Builds the MNT files: one pipe-delimited upload file per populated tab, saved to a folder you choose.

TPGC Cheat Sheet (with macro)

MNT Master Creator 2026_01

The Cheat Sheet

One row per gift card on the TPGC tab, headers on row 3, from row 4 down:

What Each Gift Card Type Creates

Every row also gets the fixed codes those tables need (action INSERT, record identifiers, voucher type and sub-type, “disallow discounts/deals/self-checkout” flags, all-stores *, and so on). The merch levels are built from the hierarchy: Group 270 → 270G, Dept 512 → 512D, Class → 512-3C, Subclass → 512-3-1S.

How to Use It

1. Open the TPGC Cheat Sheet and enable macros. Fill in one row per gift card from row 4.
2. Click Export. The macro opens the MNT Master Creator from the shared templates folder (or uses it if it’s already open).
3. Read the summary: how many cards of each type were written, the fee and property rows created, and any skipped rows with the reason (e.g. Row 7: missing/invalid Fixed Retail, Effective Date).
4. Click Yes to create the MNT files now. A check box lists each tab’s row count, last column and last cell; if anything looks wrong click No (see below).
5. Choose the folder to save to. Files are named MNTCreation_<Tab>-<username><yyyy_mm_dd_hhmm>.mnt.
6. Upload the MNT files as usual. Fix and re-export any skipped rows.

What Gets Checked

A card is only written if every check passes, so all its tabs stay in sync. Otherwise the whole row is skipped and listed in the summary.

The macro also standardises what it writes: retail as 0.00, dates as yyyy-mm-dd, and UPCs to 12 digits (restoring lost leading zeros, removing extra ones from 13- and 14-digit codes) before taking the first 11.

Example

Troubleshooting

Files & Templates

Workbooks and templates used across merchandising. Save a copy to a shared path your team can access.

Web Tools

Browser-based helpers. Files are processed on your own computer and never uploaded.

Inspection Tools

These are small, single-purpose lookup tools. Each one is its own workbook: you open it, it hides itself in the background, and then from any spreadsheet you select a column of RINs (or VPNs/UPCs) and run the tool. It looks the items up in RMS and shows the results in a pop-up window you can keep open and copy from while you work.

How These Tools Work

1. Open the tool workbook (e.g. Supplierins2.xlsm) and enable macros. It hides itself straight away (see Open Document in Background below), so it looks like nothing opened. It stays available until you close Excel.
2. Go to the spreadsheet you’re working in and select the cells with the item numbers, in one column.
3. Press Alt+F8, pick the tool’s macro (listed with each tool below) and click Run. To make it one click, add the macro to the Quick Access Toolbar (File → Options → Quick Access Toolbar → Choose commands from: Macros).
4. The results open in the RIN Inspection window, one line per item, lined up across the boxes. The window doesn’t block Excel, so you can keep working and copy values out of it.

Open Document in Background

Every tool workbook is set up so that when the file opens it turns off screen updating and hides its own window, so the tool runs quietly in the background and its macros can be used on any other workbook.

1. It’s a two-line Workbook_Open routine in the workbook’s ThisWorkbook module: it turns off screen updating and hides the workbook’s window. Any tool workbook can be set up the same way.
2. To see or edit a tool workbook again: View → Unhide, pick the workbook, make your changes, then hide it again (View → Hide) before saving so it keeps opening in the background.
3. Use case: keep several tools open at once (e.g. VPN RIN Finder and Supplier Inspect 2) while you build a deal sheet, without extra windows getting in the way.