Repgen report gives error "Run-time error '9': Subscript out of range" when querying a large number of meters

Issue

When using Repgen to generate a report for a large number of devices (i.e. > 50), it will generate the following error and the report will not complete successfully.
Run-time error '9':
Subscription out of range



Product Line
ION Enterprise
Struxureware Power Monitoring
Power Monitoring Expert

Environment
Any ION software where the excel based RepGen is used for reporting.

Cause
There is a pre-defined Named Range of cells in the report that tells the macros where to look for the names of the datasheets to process - appropriately named "Datasheets".

When a report is edited and OK is clicked, it actually deletes a bunch of empty rows out of the report. Usually this is not an issue because a report is not edited often, and normally very few queries are defined. In some cases though the situation can be exacerbated by a large amount of queries, so each time the report is edited the named range shrinks faster.

Sometimes, the Named Range is smaller than the number of queries added to the report, as in some cases of the EN50160 report.
When a large # of devices is chosen in the EN50160 report, the report can easily have > 600 datasheets as a result.

The VB macros look for all the datasheets it can find within this named range and deletes ones that it can't.
Which means, that because the Named Range Datasheets used by the macros stops around row 600, then the remaining datasheets in the report are deleted and disregarded.
Then, when the macros go looking for one of these deleted sheets again, the error 'Subscript out of range' which really means 'can t find the sheet I'm looking for' is generated.

Resolution
1.) Open the appropriate report template in excel.
Select the report in RepGen and click Preview.
-or-  Find the report in the ION install directory ...config\reports
2.)  Expand the 'Datasheets' Named Range to include more rows.
Excel 2003:
- unhide sheet |PRIVATE|Datasheets
Format -> Sheet -> Unhide
Select |PRIVATE|Datasheets
- Expand the Named Range
Insert -> Name -> Define...
- Select 'Datasheets' name.
- in 'Refers to:' field, change it to include more rows - $A3:$A606 increase to A$65000 (for example)

Excel 2007, 2010, 2013:
- Expand the Named Range
Formulas tab -> Name Manger
- Select Datasheets in the Name Manager Window
- in 'Refers to:' field, change it to include more rows - $A3:$A606 increase to A$65000 (for example)
- click Close and it will ask you to save select Yes
3.) Save the Excel document (file type should remain xls)

The report can now be edited without issue.