Develop excel macro to create duplicate rows of records based on criteria

Max points for Macro to Copy Excel Rows

Help&need to accomplish the following: Copy rows from one excel spreadsheet to another with each row being copied multiple times based upon the value in a specific column within the row.

Background/Info:

Within an active worksheet I have several records&looks something like this

        A                       B               C               D
1       # of Positions  Title           Salary  Location
2       3                       Prez            150K            NY
3       5                       Sales           25K             CA
4       2                       Ops             55K             FL

Need to take each row and copy it into a new spreadsheet (in the same workbook would be fine) so that there would multiple rows for each Title (Rows 2-4 in the above) based upon the # of positions indicated in Column A.  So the output in the new spreadsheet would look like this (Note the addition of column E which indicates a cumulate count for each title (e.g. Prez has 3 positions so the count runs 1-3 indicating each unique record):

        A                       B               C               D               E
1       # of Positions  Title           Salary  Location        Position ID
2       3                       Prez            150K            NY              1
3       3                       Prez            150K            NY              2              
4       3                       Prez            150K            NY              3
5       5                       Sales           25K             CA              1
6       5                       Sales           25K             CA              2
7       5                       Sales           25K             CA              3
8       5                       Sales           25K             CA              4
9       5                       Sales           25K             CA              5
10      2                       Ops             55K             FL              1
11      2                       Ops             55K             FL              2

There are approximately 150 sheets with similar information (standardized format...but there are rows above and below the content that may not be standardized, so will need to be able to specify that only the data in rows 10-30 (or some other range) should be copied into the new spreadsheet.

All sheets have unique names, so it may be best to create a macro that can be run by selecting a sheet and running the macro while a particular sheet is active...