VBA Needed to help add an automatic function to add a new day in the next row.

Hello, I am looking for help to add a function or formula to generate a list of days from a start date to current date and have a new day in the next row in the spreadsheet.

I am trying to put together a simple daily interest sheet where the spread sheet will generate a list of days from a given start date to what the current date might be. The daily list can go for more than 1 year. I am including a copy of the spreadheet i have so far for some to look at and see if they have a way to solve my issue with date generation for each new line item.

Thanks for Helping.Daily simple interest.xls

WYETTE,

You could use the following formula to calculate the next day.

=DATE(YEAR(A1),MONTH(A1),DAY(A1)+1)

where A1 would contain the start date.

Thank you for the respone. Tried it and it does not do exactly what i need. I have 2 cells that contain a start date and then current date. I am needing the spread sheet to auto generate a new line in the spreadsheet for each day between the start and current date. This sounds simple but it is turning out to be very hard to do. I appreciate the help.

WYETTE,

Attached is your original spreadsheet with a few additions.

Addition #1:

Cell E5 of the Simple Worksheet now contains a value. I have included a comment in the spreadsheet with an explanation of what the value means.

Addition #2:

Cell A11 (First Date in table) is now set to equal cell C4 (Start Date).

Addition #3:
Cell A12 now contains the formula I posted previously that determines what the next date is.

Addition #4:

Command Button located in Cells A7:B8

This command button first checks to make sure that cell C4 of the Simple Worksheet has value. If it does not then the VBA code stops. If there is a value then then the command button selects cell A12(contains the date after the starting date) and copies it. It then sets the cells A13 through A(whatever number is contained in cell E5) and pastes the formula into these cells.

Please let me know if you have any questions or comments.

Thanks!

RVASQUEZ

Sorry I forgot to adjust the code to copy the whole range from A:H. Attached is the spreadsheet that will add the new rows.


Thanks!

RVASQUEZ

Thank you for the help. I tried the new file you corrected. I removed all the previous dates excpet the 2 starting dates and hit the command button and nothing happened. I am using excel 2003 and when it downloaded it said it was chaning the file to a compatabile format. I don't know if that made it not work.

I do appreciate the help.

Is Design Mode under the Developer tab clicked? If so, unclick it. Also, if this isn't the case have you enabled macros and content? If not, please do so, this will enable the command button to work.

I have attached a 2003 compatible version maybe this will help.

Thanks,

RVASQUEZ

Thank you. I turned on Macros, but can not find a content turn on. I also exited out of the Design Mode. But still does not work. But thanks for all you help.

WYETTE

Okay let's try and just update your original spreadsheet:

Open your original document. Not the one I gave you.

In cell E5 type =C6+11 (since the number of rows that are currently being used before the dates are 11 rows)

In Cell A11 type =C4 (the start date)

In Cell A12 type =DATE(YEAR(A11),MONTH(A11),DAY(A11)+1)

Select Cells A13:A38 and press the delete key to clear the contents. Make sure to not delete the formulas in Cells A11 and A12.

Then go to the Developer Tab and select the Insert option from the Controls. Select the Command Button under the ActiveX Controls.

Draw a button above the table. I put it above the Date and Beginning Daily Balance cells.

Right click on the button and view code.

Copy and paste the following code in between the Private Sub and the End Sub

    If Range("C4") = "" Then
        Exit Sub
    Else
      Range("a12:H12").Select
        Range("a12:h12").Copy
            Range("a13:h" & Range("e5").Value).PasteSpecial
    End If

In the end it should look like this

Private Sub CommandButton1_Click()
    If Range("C4") = "" Then
        Exit Sub
    Else
      Range("a12:H12").Select
        Range("a12:h12").Copy
            Range("a13:h" & Range("e5").Value).PasteSpecial
    End If
End Sub

Exit out of VBA

Unclick the Design Mode Button under the developer tab. Then click the command button to see if it works.

If it works you'll need to save it as a macro-enable document.

Let me know if it works for you!

Thanks!