Renaming Tabs with Dates

**nhrav** · 06-11-2007, 04:52 PM

Hi, I work in a doctor´s office and want to document each patient that comes into my office using Excel. Preferably I´d like to have a single workbook for each calendar year, with a tab for each date Monday through Friday (like 11-Jun, 12-Jun, etc.). I could then type the patients` names and times of arrival, among the other information I track. Alternatively, I could have a workbook for each month of each year (titled for example 2007 June or 2008 August), with the tabs titled by the date of the month (like 1, 4, 10, etc).

Is there an easy way to do this without manually renaming each tab for each day of the year?

If it´s too hard to limit the macro to create tabs for only the days of the workweek, it wouldn´t bother me if the workbook had to include every day of the week (Sunday-Saturday). I can always go back and delete the unnecessary ones.

Extra credit: the office I work in is in Mexico. If there is a way to the above with Spanish dates instead of English ones?

**boylejob** · 06-11-2007, 06:13 PM

nhrav

This bit of code should do it. It definitely creates a file with a WHOLE bunch of tabs and it is fairly large (without any data). Give it a try and see if your think all those tabs in a single workbook are going to do accomplish what you want. If you think you might want to go the one workbook per month route, let me know and I will adjust the code.

Sorry, I can't help you on the Spanish part.

Please Login or Register  to view this content.

**bhofsetz** · 06-11-2007, 07:07 PM

If by Spanish dates rather than English ones you mean dd-mm instead of mm-dd then simply switch the Str(Month(dDate)) + "-" + Str(Day(dDate))

to Str(Day(dDate)) + "-" + Str(Month(dDate))

HTH

I would agree that you may want to separate each month into it's own workbook or add some type of navigation tools that will easily allow you to move to a specific worksheet from a userform that will allow you to select a date.

**nhrav** · 06-11-2007, 08:14 PM

Thanks very much for the help. I would like to upgrade my request a little bit.

* I notice that if I type something into Cell A1, and then run the macro, A1's contents aren't transferred into new sheets. I'd like the contents of the first sheet transferred into all new sheets (I think this might be able to be done with a template but I'll leave it up to the experts' advice).

* I'd like the format of the tabs to be (Date - Day), which for today (Monday = Lunes) would be (11 - Lun). The computer is formatted for Spanish, so I'm sure that it will use Spanish day names instead of English ones.

* I plan on doing this on a per month basis. I want the macro to ask me the month and the year when it runs.

Could you please change the macro to reflect those changes? I appreciate it a whole lot. Thanks.

**boylejob** · 06-11-2007, 08:25 PM

nhrav,

I'll see what I can work up for you. I cannot work on it right this minute, but I should be able to get you something by tomorrow.

**boylejob** · 06-12-2007, 05:49 PM

nhrav,

I have your program ready, but I need some information to be able to finish it. I need the path of where you want the newly created workbooks to be placed.

I also need for you to create your template and it needs to have only one sheet in it. I need to know what you name it and where you are going to keep it.

I currently have everything operationing from C:\Temp, but if you will send me the above information I will make the appropriate adjustments before I send it to you.

**nhrav** · 06-12-2007, 09:57 PM

Thanks a lot...is there a way you can just copy and paste the macro into a post? That would make it a little easier.

I'll work on the template tomorrow at work.

What's a path?

**boylejob** · 06-13-2007, 10:39 AM

nhrav,

I have attached the programs, but you are going to need to modify a few things or make sure to put your template in a certain place.

Currently, this program use C:\Temp (that is a file path) as the default directory and it is looking for a file called Patients.xlt (this is the test template I was using). It will also save all the created workbooks to that file. If you create a template, save it to C:\Temp, and call it Patients.xlt the program should run as is. Otherwise you are going to have to modify a few things.

These two lines will have to be changed. The items in red will need to be updated to where ever you store your template and to the folder you want the new workbooks to be created in.

Please Login or Register  to view this content.

I am sure a developer would rig this program so it could just prompt you for this information. Unfortunately, I am not a developer, but rather just someone who has been using Excel on a daily basis for about the paste 8 years.

When you open Tabs.xls it has a place for you to enter the starting date and how many months you want to go out from there. It will default to the current date and 1 month. I also have it set so it will not create more than 12 months at a time. I did this as a safety net so you don't end up with 50 new workbooks all at one time. If you just want to create 1 month, just enter the first day of that month and 1.

Both the months and the days of the week are in Spanish.

Hope this program meets your needs! It was a little more involved than I had anticipated, but I really like programming and I always learn something new that ends up as a benefit not only to you, but to me also.

**nhrav** · 06-13-2007, 12:39 PM

Thanks! You definitely did some hard work for this. I did what you said and it runs all the way through, except VB says there is an "Error 9 during execution: subindex not in interval" (I´m translating this from Spanish so it will probably differ from what it would say on your computer). Then it brings me to a specific part of the code and highlights it in yellow. The part in yellow is in the bottom few lines of the code: "Workbooks(sFileName).Save". However it seems to work fine otherwise.

Also, would you mind changing the prompting text in the Tabs file into Spanish? The first yellow box would say "Escriba la fecha del primer dia del mes." The second yellow box would say "Escriba el numero de meses para crear (se sugiere 1)." And the box that says¨"Create new workbooks" would say "Crear nuevos cuadernos".

By the way, I didn´t realize until now you had sent me a PM. Sorry!

**boylejob** · 06-13-2007, 02:11 PM

nhrav,

Here is the updated version of the program with Spanish instructions.

Error 9 is Subscript out of Range. Normally this means it is not able to find the file it is trying to work with. I honestly don't know what to tell you on this one.

Did you get this error right from the beginning or had you tested it a few times and then started getting the error? Is the program actually creating the workbooks for you with the sheets? If you go back to C:\Temp, do the files actually exist and if you open one, are the various Sheets there.

You might want to try deleting everything in the C:\Temp except for the .xlt file. Run the program again and see what happens. I wish I could be of more help on this, but like I said I pretty much do this on the side to make my job easier. I could probably tell you what is happening if I were there and able to watch things while they run.

Let me know what happens.

**nhrav** · 06-13-2007, 04:18 PM

Hey, I played around with the code a little and finally got it to work without any bugs. I think what might have happened is when I put the file path in the code, I didn't include the final forward slash after the name of the folder. Here's the final code I'm using:

Please Login or Register  to view this content.

Thank you SO much for doing this for me. This was the first time I used VB and wouldn't have gotten anywhere if not for your help!

**boylejob** · 06-13-2007, 05:31 PM

nhrav,

That is fantastic! I am so pleased that the program worked for you and that you were able to figure out what the problem was. You definitely picked a challenge as a first project, but now you are on your way.

It was certainly my pleasure to be of assistance.

Renaming Tabs with Dates

LinkBack

Thread Tools

Rate This Thread

Display

Renaming Tabs with Dates

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions