Go to Sheet 2 of the work book. If the 2 nd row of the worksheet has values, delete the row.

1. This presentation is based on the article at this link: http://www.dummies.com/how-to/content/how-to-run-an-excel-2010-macro.html The first action is to set up Excel’s ribbon to include a Macro button if this is not already set up. Go to File | Options and select Customize Ribbon. Check the “Developer” box, click OK.

2. Open the file Column_Values_To_CSV_File.xlsm Excel File. The cells are already formatted as Text. This is very important because Item Codes contain a capital E in them. Since the default for cells is “General” and not “Text”, the capital E can be interpreted by Excel incorrectly. In this case, Excel might convert the item code to Scientific Notation. Next, open another instance of Excel. Don’t run both files in the same instance of Excel. Open the Excel file with the item codes in it. Select the upper left cell, which will highlight the file as shown. Be sure to Enable Content if the Security Alert pops up.

3. Now, hold down the Ctrl and Shift keys and press the End key. This will highlight all active cells. Now just copy the cells and paste these into the Column_Values_To_CSV_File.xlsm Excel File.

4. Click on the upper left cell of the Column_Values_To_CSV_File.xlsm file, click the Paste Options Clipboard box. This will usually preview the data when it is moused over.

5. Go to Sheet 2 of the work book. If the 2nd row of the worksheet has values, delete the row. This will prevent any values from a previous BOM from being re-used. Also, click the Clear All button on the clipboard. Now go back to Sheet 1.

6. If the Clipboard is not already showing, click the icon shown below to display it.

7. If all values in the column are needed for the PBOM, click the column header (top cell) of the colum with the Item Codes. This will highlight the whole column. Because of internet restrictions on how long an address can be (2048 characters), a maximum of 137 item codes is all that can be run at a time. More than this number requires more reports. For multiple runs, be sure to track the row number. Pick a value like 125, 130 rows etc. If not all are going to be used, that is individual selection is required, see the next slide.

8. If not all the Material Codes in the column are needed, they can be selected individually. By selecting one at a time, or several at a time, the selections will appear in the clipboard.

9. Once all selections are made, go to a blank worksheet. Just create a new blank one should all sheets already be used. Shown below is Sheet3 of the workbook with the clipboad selections made. Just highlight a cell in the first row of any column, select Paste All and then run the macro per the steps below.

10. Select the Macros Button in the Ribbon of the Developer Tab Select the TransposeColumnToCSV macro and click Run. Note, if the sheet you pasted data from has macros, they will display in this dialog. These can be deleted if desired but if there are several, the dialog closes after each delete. That’s painful but it’s the way Microsoft designed it. If the Macro has been run before, the file it outputs may already exist on your desktop. Just over write the file if this is the case.

11. The Excel file will end up displaying Sheet 2 with its title as ItemCodesToCSV. Close the file without saving changes. Content can be cleared if changes are saved. However, in test, when changes were saved the file got corrupted after several uses. The file ItemCodesToCSV.csv file will be saved on your desktop.

12. Locate the file ItemCodesToCSV.csv file on your desktop. Open it with Notepad.

13. Open up the file and Select All.

14. Open the PBOM for LPA report. Paste these into the report, select a Spec Year and Submit.

15. The dialog box giving the option to Open, Save the PDF file or Cancel will display once the file has run. If an error comes up, such as Internet Explorer (or other browser) failing for some reason, contact us.