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
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.
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
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.
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.
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
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'?
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.
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.
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.
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.
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!
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.
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
