Insert Multiple Rows Based on Days/Month and Fill New Cells with Date and Average Values

I have a workbook that contains monthly sales totals of multiple products for several sales people. Each monthly total is entered as the first day of the month (MM/01/YYYY).

A B C D E
1 Date Sales Person Product A Product B Product C
2 1/1/2015 Bob Jones 411 731 411
3 2/1/2015 Bob Jones 409 728 413
4 3/1/2015 Bob Jones 419 938 510
5 4/1/2015 Bob Jones 408 881 406
6 5/1/2015 Bob Jones 401 900 411
7 6/1/2015 Bob Jones 413 694 413
8 7/1/2015 Bob Jones 404 451 405
9 8/1/2015 Bob Jones 409 680 465
10 9/1/2015 Bob Jones 411 752 409
11 10/1/2015 Bob Jones 411 868 431
12 11/1/2015 Bob Jones 400 668 409
13 12/1/2015 Bob Jones 407 836 410
14 1/1/2011 Ricky Martin 448 639 212
15 2/1/2011 Ricky Martin 442 838 222
16 3/1/2011 Ricky Martin 435 959 232
17 4/1/2011 Ricky Martin 380 504 201
18 5/1/2011 Ricky Martin 482 895 232
19 6/1/2011 Ricky Martin 421 787 256
20 7/1/2011 Ricky Martin 422 800 707
21 8/1/2011 Ricky Martin 425 694 200
22 9/1/2011 Ricky Martin 450 717 220
23 10/1/2011 Ricky Martin 444 841 207
24 11/1/2011 Ricky Martin 425 646 487
25 12/1/2011 Ricky Martin 402 614 431

Excel Help - 1.JPG

I am attempting to:
1) Insert a set number of rows based on the number of days in that particular month/year. **(Number of days (-1) due to day one already containing data.**
2) Auto fill new blank rows with each day of the month in the Date column (MM/DD/YYYY).
3) Calculate and insert daily average for each product (monthly total / days per month) in the corresponding new row.

Excel Help - 2.JPG

Any help or advice will be greatly appreciated.

Maybe

Please Login or Register  to view this content.

Kind regards
Leo

Thank you Leo. I am running into a Run time error '1004':

Error 1004.JPG
Debug.JPG

It runs through the last month on the spreadsheet great, but when it gets to the previous month things stop and a negative number is calculated.

Macro Run.JPG

Any suggestions?

No

in this file it works



Kind regards
Leo

I must be doing something wrong or running a different version of excel. I downloaded your spreadsheet and tried to run it with the same results. This is frustrating because I see your results tab, and it is exactly what I am trying to do.

and all dates are dates ?
possible to place the file with the error ?

Cheers
Leo

excuse the interruption but this may help:

I downloaded the file and ran the macro and it fails for me too at this line

Please Login or Register  to view this content.

Checking at the values of each variable:
ad= 1 nmdat= 01/01/2012 dat =31/12/2011
(because: ad = nmdat - dat)

So code error is due to ad = 1 resulting in vba trying to resize to (0,5)

Maybe with this changes ( marked in red)

Please Login or Register  to view this content.

Kind regards
Leoo