Naming tabs

**Amh** · 08-03-2012, 08:56 PM

I am building a workbook with each worksheet being the Sunday of the year. I have made the sheet and used a formula to pick up each date from a master sheet so the user just need to change one date to update all 52 sheets. Is there an easy way to rename each tab without retyping each tab? It would be great if the tab was the same as A1 on each sheet. For example, sheet 1 is Jan 6 2013, sheet 2 is Jan 13 2013, is there a way to do so the tab will be value of A1, and when the date change, so will the tab?
PS. I do not know how to use vba

**TMS** · 08-03-2012, 09:12 PM

Untested but something like:

Please Login or Register  to view this content.

Press Alt-F11 to open up the VBE. Insert a new module. Copy and paste the code into the new module. Press F5 to run the code.

Regards, TMS

**FDibbins** · 08-03-2012, 10:31 PM

hey TM...is that your new "monk" hair cut in your avitar?

**TMS** · 08-04-2012, 12:40 AM

@FDibbins: you like?

**Amh** · 08-04-2012, 11:25 AM

Thanks for the reply. However when I ran the program I got the following message:
Complete error
Invalid outside procedure
What did I do wrong??

---------- Post added at 10:25 AM ---------- Previous post was at 09:55 AM ----------

I am new to this forum, but it is really helpful. I reran the program with

Please Login or Register  to view this content.

. However, I found that it will work only if I type the actual value in the A1 cell, that is instead of "='Date Calc'!D4" which i used to automatically put the date on each sheet from a sheet where changing the first date changes all the sheets. If my logic is right, should I use another word other than value?

**TMS** · 08-04-2012, 11:37 AM

The code works just fine either with a value or a formula. You can't have the A1 cells on all the sheets pointing at 'Date Calc'!D4 as you would be trying to give them all the same name, which isn't allowed.

You need to press Alt-F11 to open the VBE; Insert (on the ribbon) | Module to create a new module and then paste the code into the new module. Click anywhere inside the subroutine and press F5 to run it. If you press F5 when you're not inside a subroutine, you'll get a list of possible macros to choose from.

Regards, TMS

**mike7952** · 08-04-2012, 11:40 AM

@amh,

I would guess that there was an invalid characters, More then likely a "/". Here try your Renaming Macro with this GoodSheetName function to remove any invalid characters.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**Amh** · 08-04-2012, 11:51 AM

Thanks again, but the new suggestions are above my help, sorry. I do not understand about the stings, routines, etc. SORRY.

You are correct, but each A1 points to a different cell. A1 on each sheet points to a different cell on my sheet named Date Calc. The first sheet points to D2, the second sheet to D3, and so forth. I did this so the user would only have to change the first date on the Date Calc to update all of the sheets. When I look at the date in the cell it shows the function. When I actuall type in the date (remving the function so the cell actual is text), it runs perfectly! Can I tell it to use the text and not the formula.
Am I making sense?

Thanks for all of the help from everyone. Glad I found this forum!!!

**TMS** · 08-04-2012, 12:37 PM

If you have a date in standard dd/mm/yyyy format, the slashes will be rejected. Try formatting the dates without slashes ... hyphens, underscores or spaces will be fine.

Regards, TMS

**Amh** · 08-04-2012, 12:49 PM

Tried formatting the cells as follows

dddd mmmm dd yyyy

with just spaces, but how do I input my data in the cell? the only way it lets me enter a date is 01/6/2013 and I get Sunday January 06 2013. What am I doing wrong.

I DEEPLY APPRECIATE THIS HELP!!!

**TMS** · 08-04-2012, 01:05 PM

The macro has a name ... addsheetnames.

**Amh** · 08-05-2012, 01:16 PM

TMS,
Thanks for all the help, I have not used excel since I retired and am very rusty. Would you be kind enough to continue to help once more? What is wrong with my date formatting and entry. Once that is corrected, it seems the macro should run. THANKS FOR THE HELP AGAIN!

**TMS** · 08-05-2012, 02:06 PM

Sure. Please post a sample workbook. I'd like to see the sheets and the Date Calc sheet.

Regards, TMS

---------- Post added at 07:06 PM ---------- Previous post was at 06:55 PM ----------

In the meantime, maybe:

Please Login or Register  to view this content.

Pick one: no separator, space, hyphen/minus, or underscore. Note that underscore is similar to space option but drops leading zeros.

Regards, TMS

**Amh** · 08-05-2012, 02:46 PM

Will attack workbook this evening (not at home near computer). Deeply appreciate this help. Trying to make it user friendly. Again, THANKS.

**Amh** · 08-05-2012, 07:41 PM

Attached is the workbook in rough form. I will be redoing forumals, esp ytd page. Will want to use same format for consecutive years.

**TMS** · 08-05-2012, 08:01 PM

I'll have a look tomorrow.

Regards, TMS

**Amh** · 08-08-2012, 04:03 PM

Thank TM. Did you get the worksheet? Have you had the opportunity to view it.

**Amh** · 08-26-2012, 06:54 PM

TM, did you ever look at the workbook?

**jaslake** · 08-26-2012, 07:48 PM

Hi Amh
This code is in the attached and appears to do as you require. Run the code from the Button on Sheet Date Calc.
TM was right on target.

Please Login or Register  to view this content.

**Amh** · 08-26-2012, 08:32 PM

Thanks, will try later in week and will let you know if it works.

**jindon** · 08-26-2012, 10:11 PM

Isn't it simply like this?

Naming tabs

LinkBack

Thread Tools

Rate This Thread

Display

Naming tabs

Re: Naming tabs

Re: Naming tabs

Re: Naming tabs

Re: Naming tabs

Re: Naming tabs

Re: Naming tabs

Re: Naming tabs

Re: Naming tabs

Re: Naming tabs

Re: Naming tabs

Re: Naming tabs

Re: Naming tabs

Re: Naming tabs

Re: Naming tabs

Re: Naming tabs

Re: Naming tabs

Re: Naming tabs

Re: Naming tabs

Re: Naming tabs

Re: Naming tabs

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions