Excel sheet names (1 Viewer)

mahen

Registered User.
Local time
Today, 11:49
Joined
Jan 13, 2008
Messages
56
Does anyone know a shortcut way how to change sheet names.
Eg. i have sheet names as 1dec08, 2dec08, 3dec08 and so on until 31dec08. I want to create a news excel file using the above file, but changinge all sheets names as 1jan09, 2jan09 and so on.
thanks
- Mahen
 

Bilbo_Baggins_Esq

Registered User.
Local time
Today, 06:49
Joined
Jul 5, 2007
Messages
586
This will need code.
What version of Excel are you using?

Is this to be done at a specific time (e.g. end of month to create the next months file), or is this to be user initiated at any time?

If user initiated, you may want a dialog box to get the user to provide the year and month desired (since there are different number of days per month based on month and year).

Are you really sure you want to sort your data this way?
I'm not sure what you're doing with it, but it seems plasuable that most purposes could be met by simply adding a date reference column to a main data table with all data in it.
 

boblarson

Smeghead
Local time
Today, 04:49
Joined
Jan 12, 2001
Messages
32,059
1. Go to Tools > Macros > Visual Basic Editor

2. Insert a new Module

3. Copy this into that module
Code:
Function ChangeSheetYear()
   Dim sht As Worksheet
 
For Each sht In ActiveWorkbook
   sht.Name = Left(sht.Name,Len(sht.Name)-1) & "9"
Next sht
Then go to the Immediate Window and type ChangeSheetYear and press Enter.

This is air code (untested) but it should work.
 

mahen

Registered User.
Local time
Today, 11:49
Joined
Jan 13, 2008
Messages
56
Bilbo,
I am using excel 2003, I don't need to wait till end of the month. I can start anytime.

Boblarson,
Your method seems to be interesting. I will try and let you know.

thanks.
- Mahen
 

mahen

Registered User.
Local time
Today, 11:49
Joined
Jan 13, 2008
Messages
56
Boblarson,
Your code didn't work. Here is my file. Also my email nlmahen@ntlworld.com.

thanks
- Mahen
 

Attachments

  • diary.zip
    5.1 KB · Views: 234

boblarson

Smeghead
Local time
Today, 04:49
Joined
Jan 12, 2001
Messages
32,059
1. Go to the VBA window and Insert MODULE.

2. Paste this corrected code in and run it:
Code:
Function ChangeSheetYear()
    Dim sht As Excel.Worksheet

    For Each sht In ActiveWorkbook.Worksheets
        sht.Name = Left(sht.Name, Len(sht.Name) - 1) & "9"
    Next sht
End Function
 

Bilbo_Baggins_Esq

Registered User.
Local time
Today, 06:49
Joined
Jul 5, 2007
Messages
586
Hey Bob, isn't that code just changing the year?

The OP's example seems to need to detect:
1. the year for the new sheets
2. the month for the new sheets
3. the number of days in the new month

Have you got some pre-written code for these criteria you can adapt to creating new sheeets?

Mahen,
I've forgetten about htis one, sorry.
If Bob has something already, that may be faster.
If not, I'll throw some code together for it later tonight.
 

boblarson

Smeghead
Local time
Today, 04:49
Joined
Jan 12, 2001
Messages
32,059
Hey Bob, isn't that code just changing the year?

The OP's example seems to need to detect:
1. the year for the new sheets
2. the month for the new sheets
3. the number of days in the new month

Have you got some pre-written code for these criteria you can adapt to creating new sheeets?

Mahen,
I've forgetten about htis one, sorry.
If Bob has something already, that may be faster.
If not, I'll throw some code together for it later tonight.

All it looked like to me from the original post was that they had a workbook with each day of the year there (or close) and that they wanted to take the existing workbook and change the year on the tabs so they could use it for this year. I don't see any requirement to add new sheets, at least based on the original statement.
 

boblarson

Smeghead
Local time
Today, 04:49
Joined
Jan 12, 2001
Messages
32,059
But if they want to add a worksheet for each day to either a new workbook or existing one (provided it will let them add that many sheets to an existing one):

Code:
Function AddAllSheets()
    Dim strName As String
    strName = ActiveSheet.Name
    Dim intcount As Integer
    Do Until intcount = 365
        ActiveWorkbook.Worksheets.Add , Worksheets(strName)
        ActiveSheet.Name = CStr(Day(DateAdd("d", intcount, #1/1/2009#)) & Format(DateAdd("d", intcount, #1/1/2009#), "mmm") & "2009")
        intcount = intcount + 1
        strName = ActiveSheet.Name
    Loop
End Function
 

wiklendt

i recommend chocolate
Local time
Today, 22:49
Joined
Mar 10, 2008
Messages
1,746
Hi bob. just wondering if you can help me on something similar.

This is my very first attempt a VB in any application outside MS Access! (so i'm completely lost) i'm using excel 2007.

background
i have setup a template for work, which calculates data and generates graphs based on the first sheet in the workbook (which i've called RAW). I have then, for the four available channels. For each channel, there is a "CH1 DATA" and a "CH1 CHART" (or CH2 or CH3 or CH4). the CHART sheets display a nice chart based on the DATA sheet, which gets values and calculates things based on the RAW sheet.

people open this workbook, paste in raw data on the first (RAW) sheet, then excel automatically calculates this and that on my other sheets and fills graphs.

when the person wants to print these graphs i have the sheet name printing as the header text on the page. i.e., i have headers and footers setup with automatic text (like: &[path]&[file] | &[tab]) to make it easier to see where this chart/data came from.

at the moment, it's not very helpful to have just CH1 CHART, since each experiment they do on this machine can have different dyes in each channel to another person's experiment (e.g., one person may use "Cy3" in their first channel, then "FAM" in their second, while another person may use "Cy3" and "Texas Red", what's more, is person one can use different dyes in their next experiment, despite using the same number of channels as before).

So i've made a new sheet called "Channels" where i will get users to enter the dyes they're using in each and i want this to be reflected in the chart print.

i was originally thinking about changing the sheet names for the charts (so i started to reply to this thread), but maybe that's a little messy. perhaps it will be easier to just change the header/footer text in the chart prints...? i.e., something like

Code:
For "CH1 CHART" Sheet.Header = Channels!B2 & " Chart"
if i can change the sheet name for the graph depending on what channel name they put into the "Channels" sheet in the appropriate cell, then the print will be more useful, like "Cy3 Chart" and "FAM Chart", where the "Cy3" or "FAM" is what they enter into CHannels!B2 and Channels!B3... up to four channels available at the moment.

this is to happen 'on the fly' - i.e., the user can change the value of the cell in Channels!B2... and the sheet name (or header text, whatever's easier/better) should change accordingly.

i've also tried (adapted from developer help in excel):
Code:
With Charts("CH1 CHART")
    .HasTitle = True
    .ChartTitle.Text = Channels!B2
End With
but i don't see any changes whatsoever. (i have saved as a macro-enabled workbook).

now, one last thing i just thought of: i have this chart as its own worksheet (you know, where excel shows only the chart, and it's not really on a 'sheet') - so, to refer to this in VB, is it a 'Worksheet' or a 'Chart'?
 

Bilbo_Baggins_Esq

Registered User.
Local time
Today, 06:49
Joined
Jul 5, 2007
Messages
586
You cannot edit charts with standard VBA in standard Excel 2007.
Chart elements are READ ONLY with VBA in Excel 2007.
This is by design and due to the new Office Art ownership of Office graphics.
You can actually demonstrate this yourself by trying to record a macro in which you perform some chart edits.
You will find that any steps you took regarding edits in the chart will not have been recorded.
 

Bilbo_Baggins_Esq

Registered User.
Local time
Today, 06:49
Joined
Jul 5, 2007
Messages
586
I would suggest that you place the values you want represented in the chart, in cells in the workbook. Reference those cells in the chart title, etc. Then, instead of editing the chart, edit the cells and the chart will reflect the changes.
 

Bilbo_Baggins_Esq

Registered User.
Local time
Today, 06:49
Joined
Jul 5, 2007
Messages
586
I have struggled with this "official" feedback regarding editing charts in Excel 2007, but as yet, I have not found anybody who has successfully done it in VBA from within Excel.

However, I did find this page regarding editing Excel 2007 Charts using Visual Studio 2005 with VB.NET and the Microsoft Excel 12.0 object Library
http://msdn.microsoft.com/en-us/library/bb404904.aspx
 

wiklendt

i recommend chocolate
Local time
Today, 22:49
Joined
Mar 10, 2008
Messages
1,746
Last edited:

wiklendt

i recommend chocolate
Local time
Today, 22:49
Joined
Mar 10, 2008
Messages
1,746
You cannot edit charts with standard VBA in standard Excel 2007.
Chart elements are READ ONLY with VBA in Excel 2007.
This is by design and due to the new Office Art ownership of Office graphics.
You can actually demonstrate this yourself by trying to record a macro in which you perform some chart edits.
You will find that any steps you took regarding edits in the chart will not have been recorded.

thanks bilbo, good to know, albeit sad to hear - especially the "the is by design" part.... grrr!
 

wiklendt

i recommend chocolate
Local time
Today, 22:49
Joined
Mar 10, 2008
Messages
1,746
got it: auto chart title update ;)

it's been such a long time since i used excel to do more complex things that i forgot the formula bar is available, i'm used to just referencing the 'fields' in access directly in the text box... meh!
 

Bilbo_Baggins_Esq

Registered User.
Local time
Today, 06:49
Joined
Jul 5, 2007
Messages
586
ahh, I was actually just in the midst of typing that out but you beat me to it.

Edit note:
Remember, that since now the text box is also part of Office Art, you cannot edit the text box directly, but you can edit the reference shown in it.
 

mahen

Registered User.
Local time
Today, 11:49
Joined
Jan 13, 2008
Messages
56
Hi Bilbo,
Not working. Can add on to my excel file I sent you earlier please.
Basically I create new excel file for each month. But change the sheet Name as day of that month. 31 sheet for 31 days. File name would be different for each month. At the moment. Copy the file to create dublicate and rename all 31 dates manully. I need a shortcut to do that. please.
- Mahen
 

Bilbo_Baggins_Esq

Registered User.
Local time
Today, 06:49
Joined
Jul 5, 2007
Messages
586
Mahen,
Here is a workbook that should meet your needs.

I have tested it to properly catch the end of the year, various different numbers of days in the month, and leap years.

However, I have little doubt that Bob could probably present a leaner string of code to accomplish the same tasks.

Enjoy!

-------------------------------------------

Bob,
I look forward to your expertise if you've the time for it.
 

Attachments

  • Create_New_Month_Workbook.xls
    36.5 KB · Views: 136
Last edited:

Users who are viewing this thread

Top Bottom