Needs_ver2.xls

Needs_ver2.xls is a copy of the new Needs.xls workbook. Grantees who already have data in the CPMP 1.3 Needs.xls workbook have two options: 1) copy and paste data as necessary into the new workbook, or 2) update their existing workbooks to include the new functionality. Instructions for the latter are provided below.

Grantees that choose to update their existing workbooks to include the new functionality will be required to run a macro. This macro modifies ONLY the HSGNeed and CommunityDev worksheets within the Needs.xls workbook. Data on all worksheets in the workbook will be preserved. After this worksheet has been updated, grantees should review their workbook to verify changes before saving.

NOTE: This macro should only be run ONCE! You can run the macro from any of the worksheets. When the macro is complete, you will be moved to the HSGNeed worksheet and a prompt will display.

Screen shots and instructions provided below are from MS Excel 2000. Screens and prompts may differ slightly for other versions.

To Incorporate the New Needs.xls Functionality into an Existing Needs.xls Workbook:

1. Download CPMP 2.0.
   
   
2. Follow the instructions for downloading CPMP 2.0, including extracting the files.
   
   

   Important! It is highly recommended that you extract the CPMP Version 2.0 files to your LOCAL HARD DRIVE to run the macros. If the files need to be housed on a network, they can be copied to their permanent destination after the data migration.

   
   
3. Once CPMP 2.0 is installed on your computer, copy your current Needs.xls workbook to the folder named Ver2.0_Data Migration, which is located in the same folder to which you extracted CPMP 2.0.




Note: It is recommended that you close all other applications, other than Microsoft Excel. Therefore, printing this page to complete execution of the macro is suggested. Also, if you have Excel's Auto Save feature installed (under the Tools menu), it should be turned off temporarily.




Important! It is important that you COPY your existing Needs.xls workbook to this folder. This will allow you to review the changes before saving and revert back to the original should you encounter difficulty.



4. You must now open two Excel workbooks from the Ver2.0_Data Migration folder:
   
   
   1. First, open Needs_ver2.xls.
      
      
   2. Next, open Needs.xls.
   
   
   

Note: You may receive 2 prompts for each workbook. Click Enable Macros for each workbook and click No to update links to other workbooks. (If you are not prompted to enable macros, ensure your security settings are set at Medium.)



5. In Needs.xls, on the Menu bar, select Tools. Then select Macro, and Macros from the submenu.
   
   
   
   
   
6. In the Macro dialog box, select the Needs_ver2.xls!mcrImplementChanges macro, then select the Run button.
   
   
   
   
   
7. The macro should complete within a couple of seconds. You should see the following message, "Data migration complete."
   
   
   
   
   
8. Click OK to the message and verify that the following changes were made:
   
   
1. CommunityDev worksheet now has additional rows appended to the end for CDBG and HOME:
   
   
   
   
   
2. The CommunityDev worksheet title in cell A8 has changed to read "Housing and Community Development Activities."
   
   
3. On the HSGNeed worksheet, the cell Q5 label has changed from "Cumulative" to "Multi-Year," and the Goal column underneath has changed to an editable field, allowing grantees to enter data.
   
   
4. At the bottom of the HSGNeed worksheet, the last 6 cells for Total 215 Renter, Total 215 Owner and Total 215 (Q128 through R130) have been changed from empty cells to fillable and calculated cells, as appropriate.
   
   
   
   
   
9. Choose File | Save.



10. Close Needs.xls and save the changes. Also close Needs_ver2.xls and choose No when prompted to save changes.



11. You may now copy Needs.xls up one folder to the CPMP_ver2 folder. Choose Yes to overwrite the Needs.xls in the folder.