Macro to save workbook at the end of the year

[enxocoebl]

I need a macro to save (preferably move) a workbook (used workbook) at the end of the year and start with a fresh workbook (unused copy).
I need it to be saved to a folder with the name of the year it was created.
I can open the new workbook manually no problem.. But i may forget the date and continue using the old workbook in the new year.
Thank you in advance

 

[Sal Paradise]

In your directory, just create a folder called "Template" with the blank workbook inside.

When you open the file, record the date in a name or a cell (just set the value of the name/cell to NOW() with VBA on open).

Then before setting that name, whenever you open the file, you should check the date stored vs. today's date, and if the year is different, you can create a folder, move the current file to that folder and close, move the template file to your main directory, and open that file.

Presto!

 

[enxocoebl]

hey i dont know how to do this part of what you said (record the date in a name or a cell (just set the value of the name/cell to NOW() with VBA on open).

Then before setting that name, whenever you open the file, you should check the date stored vs. today's date, and if the year is different, you can create a folder, move the current file to that folder and close, move the template file to your main directory, and open that file)

can you explain it a bit in simple terms i am not familiar with vba or macros.. thank you.

 

[Sal Paradise]

The concept is simple.

You want to create a new book for each year.

That means:
1) You need to know when it's a new year
2) You need to move the book when you know it's a new year

For step 1, you need to know when the last time the file was opened was.

To do that, VBA has a function called NOW() that will tell you what the time is now. But to have that stay in your workbook when it is closed, you need to save it somewhere. So save the value of NOW() in a cell or in a name.

Then, once you know when the file was opened last, you can compare that to the new time when you open the file (using NOW() again), and if the year of the last time it was opened is different from the year today, then you go in to step 2.

For step 2, you want to start working on a new workbook in the new year. So first you need to save the current workbook to a folder for last year, then create the new file and open it so you are working on the current year.

If you don't know how to do any of this and don't even know where to start learning how, then perhaps you should just do it manually for now, or hire someone to do it for you?

 

[enxocoebl]

hey sal paradise,
i made this entire workbook(6 worksheets.. almost all of the functions available in excel) by myself out of the things i learned from mr. excels youtube videos.
i am 28 now.. my learning days are over.. i had asked for help once before when i needed to do something that could only be done by a macro (the only macro in the workbook). the guy who helped me (hermanito) wrote a code for me that worked perfectly(he said it was a piece of cake).
i tried to learn from it and by checking what code is written when i record a macro.. but it was not very easy
for example i dont know what dim means.. i have not seen it in a recorded macro.
and thanx for showing interest in this question and for your quick replies.. i ll just ask my sis to do it manually after all its only once a year thanks mate.

 

[Sal Paradise]

I'm 32 and still learning.

"Dim" just creates a variable of a certain type.

Most stuff can be found through various sites, or through youtube videos, and if you give a good shot at one of the portions, and ask for help on the code, people are usually more than willing to help bit by bit. But when the request is just, "I want code to do this." people are often more hesitant to do it, since they are concerned you will expect them to continue to update it whenever there is a change or you want another feature, since you haven't created any part of it yourself.

Doing it manually is no big deal for a small task, and it isn't that tough of a macro, you just need to try to figure out how to do it, and ask for help when you get stuck by posting your effort so far and asking people for support to help you learn it, rather than to just fix it for you.

 

[enxocoebl]

thanks man. i ll do it manually.