Hello all who read,
I am designing a work book that tracks attendance, among other things. It is set up for a year period of time i.e. 12/17/13 to 12/16/14. This causes a 13 month period. I need to tab names to reflect the month and year because of beginning and ending in the same month.
I have tried many different codes, etc... but can't get any to work consistently.
Any ideas?
I'm not sure exactly what you are trying to do, but this will set the tab (worksheet name) to a month/yyyy format...
you can replace the date to the Now() function....HTH
Please Login or Register to view this content.
Ernest
Please consider adding a * if I helped
Nothing drives me crazy - I'm always close enough to walk....
Look at this thread. I believe this is very similar to what you want to do. I would only change the value of i = i + 7 to i = i +30 and the start date.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
alan, which thread are you referencing please
Sorry, another senior moment in my life.
http://www.excelforum.com/excel-prog...ate-range.html
My workbook has 13 sheets. Each sheet represents a month/yr. Each sheet has a list of the 13 consecutive month/yr in cells p6 through p18. These cells automatically change based on a begin date that I put in manually in another cell. I also need the sheet names to change when p6 through p18 change (of course each sheet name would be based on contents of 1 cell in that range).
Essentially, I need the sheet names to change based on a cell value and update automatically when cell value changes.
Clear as mud?
Which cell would dictate the sheet name?
Would that be the same cell on each sheet that would dictate the sheet name?
I'm thinking that this is something that can be accomplished with the Worksheet Change Event.
Give me more information and I will try to give you some code for it.
Ok. Got it.
Open your VBE by selecting Alt + F11
In the left window, you will see all your sheets.
Click on the first sheet and the window on the right will appear blank.
Insert this code in the right window and save.
Repeat for each of the sheets. For each sheet, change in the code the sheet number.
Change the range for the location of the cell on each sheet that holds the value that the sheet should be named.
Please Login or Register to view this content.
First, I do not have Excel2010 but 2007 .I put the wrong Excel information in my profile but have now updated my profile.
Regarding your help above, thank you, however, when I use the alt F11 command it inserts a new sheet named Macro 1 and I did not know what to do from there.
So, I copied and pasted the code above and changed the sheet information and the range (cell) but nothing happened.
Also, the range (cell information) is a date. Jan-14 format
When you press Ctl + F11 you get the page you mentioned. Try again holding down the Alt key and pressing F11. Or if you have the Developer tab enabled on the ribbon, go there and click on the Icon for VBA Code. Code will work on 2007 or 2010. No difference.
Alan
I have tried so many ways and codes but can only get them to work once.
I need the sheet names to update anytime the begin date changes on the 1st sheet. When the begin date changes then a simple formula updates cell B1 in each sheet giving each a name of one of the 13 months/yr, sequentially . I need the tab names to update according to contents in B1 on each of the 13 sheets anytime the begin date on sheet 1 changes. Also, sheet 1 would be renamed as the 1st month/yr in the sequence.
I feel I am just going to give up...
Perhaps, it is time for you to upload your Workbook so that it can be analyzed and the code perfected. Be sure to sanitize it for any confidential material.
Let me make sure I understand correctly. When B1 changes in the first sheet, you want all subsequent sheets to change the value of B1 and then the sheet names will change to that value. Is this correct?
how do i upload the document?
by sanitize you are referring to general information in the worksheet itself?
Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
By sanitize, I mean that you should change any confidential data to dummy data.
I hope I attached it correctly.
Right-click the tab for first sheet, select 'View Code' option then paste this code:
Please Login or Register to view this content.
- Please remember to mark threads Solved with Thread Tools link at top of page.
- Please use code tags when posting code: [code]Place your code here[/code]
- Please read Forum Rules
Thank so very, very much! This works great!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks