write a program or macro for auto populating dates

[emondragon07]

All,

I'm trying to create a table that auto populates dates horizontally per column. What I'd like to be able to do is be able to extend the duration if needed simply by inputting the end date In a fixed cell. For example cell A1 has the start date and B1 the end date. I want to start the project on 1/3/2013 . I will type this in A1, and I want to end the project on 2/4/2014. The second date goes in B1. Once that's done those dates should populate the worksheet. I've been trying to figure this out but can't make it work. Any help would be appreciated.

Thank you.

[stanleydgromjr]

emondragon07,

With cell A1 = 1/3/2013 formatted as "m/d/yyyy"

And, with cell B1 = 2/4/2014 formatted as "m/d/yyyy"


The below macro will clear row 2, and then fill in all the dates into row 2.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the AutoPopulateDates macro.


If you need the dates to fill in row 1, then let me know, and, I will change the macro.

[emondragon07]

Stan,


Thank you very much for your response I will go into my workbook and try this out! I will let you know my results!! Have a good one and thanks again.

[stanleydgromjr]

emondragon07,

Same worksheet starting information.


The following macro is so much faster then the one above.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the AutoPopulateDatesV2 macro.

[emondragon07]

Stan,

I've used both and they work great! One last question. I was trying to change the code so it does exactly the same thing but that instead the start date is inputted in E1 and end date in E2, with the dates being populated in row 4 cell D4. I was able to change the interval from day to day, to every 7 days by changing the locations in the code with + 1 to + 7, but that's the only part I was successful in. If you can help me out with this I would greatly appreciate it. Thanks again!

emondragon07

[stanleydgromjr]

emondragon07,


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the AutoPopulateDatesV3 macro.

[snb]

or

Please Login or Register  to view this content.

[stanleydgromjr]

snb,

Very nice, short, and extremely fast. One for the archives.

But, the output should be in row 4 beginning in cell D4, and going to the right.

I do not understand your code, so, could you adjust it accordingly?

Thanks in advance,
Stan

[snb]

to give a clue:

Please Login or Register  to view this content.

[stanleydgromjr]

snb,

Thanks again. One for my archives.

[snb]

You are, you were and you will always be welcome

[emondragon07]

snb,

Is there any way to make this run by every 7 days instead of by month? Say i input 1/1/2013 the next date should be 1/8/2013 and so on until 6/4/2013. Also, could the month input format be m/d/yyyy? If you can help with this I would greatly appreciate it. I have gotten all the code's in this thread to work but can't seem to make it populate in 7 day intervals.

emondragon07

[emondragon07]

Stan,

As I mentioned in an earlier post #5 I was able to change to interval in the code in post #4 & #3 from day to day to every 7 days. I thought I had done it correctly but now that I try it with this Macro AutoPopulateDatesV3 it's not coming out correctly. I input start date a 1/1/2013 and end date at 6/4/2013. I was hoping it would populate every seven days 1/1/2013, 1/8/2013....all the way down to 6/4/2013. However, although it did go down in 7 day intervals it populated far beyond 6/4/2013. The way i plan to use my spreadsheet is with dates every 7 days based on the dates I input for start/end. Can you help with this version I would greatly appreciated it and that would be the last step I need to have my spreadsheet function fully. Thanks once again,
Emondragon07

[emondragon07]

Stan,

As I mentioned in an earlier post #5 I was able to change to interval in the code in post #4 & #3 from day to day to every 7 days. I thought I had done it correctly but now that I try it with this Macro AutoPopulateDatesV3 it's not coming out correctly. I input start date a 1/1/2013 and end date at 6/4/2013. I was hoping it would populate every seven days 1/1/2013, 1/8/2013....all the way down to 6/4/2013. However, although it did go down in 7 day intervals it populated far beyond 6/4/2013. The way i plan to use my spreadsheet is with dates every 7 days based on the dates I input for start/end. Can you help with this version I would greatly appreciated it and that would be the last step I need to have my spreadsheet function fully. Thanks once again,
Emondragon07

[stanleydgromjr]

emondragon07,

Can I see the macro code that you wrote for the every 7th day?


If posting VBA code, please use Code Tags - like this:

[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code]

[emondragon07]

Stan,

Yes, It is exactly the same code in version 3 with the following changes to Line 8:

Please Login or Register  to view this content.

and Line 11:

Please Login or Register  to view this content.

The number 7 used to be a 1 in your original code. Maybe there are other pieces of the code that needed to changed to accomplish my goal?

Thanks the the response.

[stanleydgromjr]

emondragon07,


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the AutoPopulateDatesV4 macro.

[emondragon07]

Stan,

You are a life saver! This works perfect just what I need.

Thank you very much!

emondragon07

[stanleydgromjr]

emondragon07,

Thanks for the feedback.

You are very welcome. Glad I could help.

Come back anytime.