Sheetname

Hi, 

could any one help me with the following:

I want to change a sheetname with a formula. Is it possible and if yes
how?

Thanks a lot

--
Message posted from http://www.ExcelForum.com

0
12/15/2003 5:58:59 PM
excel 39879 articles. 2 followers. Follow

1 Replies
556 Views

Similar Articles

[PageSpeed] 8

Not with a formula - you'll need to use VBA. This should do it:

Sub a
Sheets("sheet1").Name="AnotherSheetname"
end sub

-- 

Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"


"maxximuss" <maxximuss.yhppz@excelforum-nospam.com> wrote in message
news:maxximuss.yhppz@excelforum-nospam.com...
> Hi,
>
> could any one help me with the following:
>
> I want to change a sheetname with a formula. Is it possible and if yes,
> how?
>
> Thanks a lot!
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
Andy
12/15/2003 7:36:38 PM
Reply:

Similar Artilces:

How do I test whether a defined sheetname exists in a closed workbook?
Hi... I've been searching all over and can't find anything on this particula challenge so I thought you guys might be able to help! I'm wondering if anyone knows how to test whether (or not) a specifi sheetname (e.g. "TestSheet") exists in a closed workbook? (I'd really prefer not to have to open the workbook if at all possibl as its one step in an intensive consolidation process from upto 10 workbooks.) Thx muchly! ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http:...

Sheetname
Hi, could any one help me with the following: I want to change a sheetname with a formula. Is it possible and if yes how? Thanks a lot -- Message posted from http://www.ExcelForum.com Not with a formula - you'll need to use VBA. This should do it: Sub a Sheets("sheet1").Name="AnotherSheetname" end sub -- Regards Andy Wiggins www.BygSoftware.com Home of "Save and BackUp", "The Excel Auditor" and "Byg Tools for VBA" "maxximuss" <maxximuss.yhppz@excelforum-nospam.com> wrote in message news:maxximuss.yhppz@excelforu...

date string as sheetname
I have a macro that copies the last sheet to a new sheet at the end. I would like to note the date that is in A2 prior to the copy, and add one month to it. This result will then become the name of the new sheet in the form 1-Oct-05 or something equivalent (ie: 1 Oct 05, 1Oct05,...) How can I get the date from A2, and how can I modify it to the correct string? Thanks. Hi Keyser, Try: '==================>> Sub Tester() Dim WB As Workbook Dim SH As Worksheet Dim sStr As String Dim i As Long Set WB = ActiveWorkbook '<<======== CHANGE i = WB.S...

Returning Sheetname
Hi, Probably an easy one but I can't work it out. I want to get the curent sheet name and place it in cell A1. I thought I could use something like '=CELL("Sheetname",A1) but no. -- Andrew Andrewm try this, file must be saved first =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Andrew" <Andrew@discussio...

Sheetname in a cell
Will someone please help me with a formula. Lets say I want cell G8 to reflect the sheetname it is in. Thanks, Hi =MID(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1))+1;FIND("]",CELL(" filename",$A$1))-FIND("[",CELL("filename",$A$1))-1) returns workbooks name =MID(CELL("filename";$A$1);FIND("]";CELL("filename";$A$1))+1;99) returns worksheets name Arvi Laanemets "daniel chen" <danchen@worldnet.att.net> wrote in message news:hNOnd.41533$7i4.14181@bgtnsc05-news.ops.wor...

Sheetname #2
I want to show the sheetname in a cell. What formule/funtion to use? Exampel: The sheet is named "Test". I want this name to appear in cell A1. -- monor ------------------------------------------------------------------------ monor's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30308 View this thread: http://www.excelforum.com/showthread.php?threadid=499721 The workbook must be saved =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32) -- Regards, Peo Sjoblom "monor" <monor.21f8cn_11369004...

Sheetnames in formula
I have 3 sheets (named 2001, 2002, 2003 representing different years) and a sheet named variance. All have identical row/column headings. In the sheet variance, I'd like to supply names of two sheets and calculate the variance between them. I could create a different sheet for each variance (2003-2002, 2002-2001, 2003-2001) but I don't want to do that. Any ideas? Hi Frank With 2002 in A1 and 2003 in A2, this formula sums their cell C7: =INDIRECT(A1&"!C7")+INDIRECT(A2&"!C7") HTH. Best wishes Harald "Frank" <anonymous@discussions.micr...

Display Sheetname in a cell
I'd like to display a worksheet name "with the body" instead of "in the margins" (ie, using Header / Footer definitions). Is there a built-in mechanism for picking up the worksheet name in a formula? I tried putting Function GetSheetName() As String GetSheetName = ActiveSheet.Name End Function in a code module and " = GetSheetName() " in a cell; but quickly discovered that <F9> doesn't cause the user defined function to re-evaluate. Suggestions? -- Clif Try this: =MID(CELL("filename",A1),FIND("]",CELL("fil...

Sheetnames in Excel
Hello: When I get the sheetnames from Excel, I get two refrences to a sheet. For example, I will get Sheet1$ Sheet1$_ What is the second one. Is it refrencing the same table? Thanks Prasun Are you referring to ADOX? -- HTH RP (remove nothere from the email address if mailing direct) "Prasun" <prasunp_@csufresno.edu> wrote in message news:egE1t4yWFHA.3828@TK2MSFTNGP10.phx.gbl... > Hello: > > When I get the sheetnames from Excel, I get two refrences to a sheet. > > For example, I will get > > Sheet1$ > Sheet1$_ > > What is the second ...

Formula that returns the sheetname
Does anyone know of a formula that would return the active sheetname? http://www.mcgimpsey.com/excel/formulae/cell_function.html "TimT" wrote: > Does anyone know of a formula that would return the active sheetname? Great Link! The formula was =MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255) ....yeah, like I was gonna figure that one out on my own. "David Hepner" wrote: > http://www.mcgimpsey.com/excel/formulae/cell_function.html > > > "TimT" wrote: > > > Does anyone know of a form...

Help getting SheetName into a cell
I need to refer to the sheet name in a formula and can't figure out how to do it. I can't find a Function which will do this. I did discover ActiveSheet.Name but I have been unsuccessful in getting it work in a function. Specifically I have sheets named "1.a" , "1.b" , "1.c" . . . "99.a" , "99.b" , "99.c" and need to get these names into cell C3 of each sheet. The sheets may not be in order and there may be missing sheets. Thanks in advance for any help. Omar Hi You could put somthing like this in a macro or attatch ...

Getting sheetname that calls a macro.
Is it possible to get the sheet name that a macro is called from. I am using the code below to make a backup when the macro is called but it can be called from any of 31 sheets. Code = ThisWorkbook.SaveCopyAs ThisWorkbook.Path & "\Backup\Sales\Work\Change_Retails " & Format(Now, "dd-mm-yy hhmmss") & ".xls" End of code I would like to include the sheet name in the backup name -- Thank You in Advance Ed Davis ThisWorkbook.Name will give you the name of the active workbook. Chris "Ed Davis" <ed@danverssoftware.com> wrote in me...

Using sheetname variable in a formula
Hello. Suppose a cell (say A1) contains the name of a valid worksheet (sa Sheet1, Sheet2, and Sheet3). I would like to use the *contents* of cell A1 in my formula to get a data on that sheet. Something like: =*SheetNameStoredInCellA1*!A23 I know *=Sheet2!A23* is valid but when I try =A1!A23, that does not work Hope someone can help -- mm ----------------------------------------------------------------------- mms's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1653 View this thread: http://www.excelforum.com/showthread.php?threadid=31386 ...

can we copy our "sheetnames" too?
Hi, The below code copies the content of our workbook sheets seperately as new workbooks, but saves the sheets with default "Sheet1", "Sheet2" name format. Can we also copy the used sheet names while copying? TIA Hi no code attached :-) -- Regards Frank Kabel Frankfurt, Germany Martyn wrote: > Hi, > The below code copies the content of our workbook sheets seperately > as new workbooks, but saves the sheets with default "Sheet1", > "Sheet2" name format. Can we also copy the used sheet names while > copying? > TIA Hi, This is on...

Use combobox value as sheetname to set sheet object
Greetings all. I've got a userform with a combo that is populated with the workbook sheet names with code I found here... For Each sh In ActiveWorkbook.Sheets Me.ComboBox1.AddItem sh.Name Next I would like to set a sheet object using combobox1, but I can not figure it out. Basically this is what I want to do Dim Ws as worksheet Set Ws = Activeworkbook.sheet(combobox1). Now I know this does not work, but I can't figure out how to do it. My first thought was to have the combo box be 2 columns, one of which would contain the index number of the ...

Auto insert sheetname into chart title?
Hi I'm using Excel 2003 I have several charts on a worksheet. At the end of each month I copy the sheet to become the first worksheet in the workbook, delete the data and rename the sheet for the following month. All my charts have a chart title e.g "Total income for April 2010". The sheetname is April 2010. When I copy this sheet to become "May 2010" I then have to manually edit all the chart titles on that sheet. I wondered if there is a way to reference the sheetname in the chart title so that when I rename the sheet the chart titles auto...

Using sheetname variable in a formula #4
Oh, you posted another solution before I finished mine. Let me try this new solution -- mm ----------------------------------------------------------------------- mms's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1653 View this thread: http://www.excelforum.com/showthread.php?threadid=31386 ...

Using sheetname variable in a formula #3
No that didn't work... but got me to experiment... ' ' A-325 ' works (<single quote>, <single quote>, <A-325>, <single quote>) Thank you very much!! Do you know WHY this works, because than maybe I can alter somethin else in the INDIRECT call, as I'm sure the users of this sheet wil balk at having to enter 3 single quotes in addition to the A-325 etc. Please understand, I am not complaining about your solution, as I a delighted I got it working at all. Thanks again! -- mm -----------------------------------------------------------------------...

Using sheetname variable in a formula #2
Yes that WORKS!! Thank you! One minor glitch however. My *desired* sheet name is *A-325* and the like (A-490, etc) Your code does not work for that name, however if I change it to *A325 it works. Any ideas why this is happening -- mm ----------------------------------------------------------------------- mms's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1653 View this thread: http://www.excelforum.com/showthread.php?threadid=31386 Hi should work. Have you included the apostrophes ' in the formula. Post the exact formula you have used -- Regards Fr...

Using cell text as a Sheetname in a formula
Firstly, I have a large spreadsheet consisting of 1250 sheets. I have one main sheet, listing all the sheetnames in column A. Here's a small example of the first 4 rows in column A: Cell A1: T014-06784 Cell A2: T014-06784G Cell A3: T014-06785 Cell A4: T014-06785G I want to use a formula in column B of my main sheet, that uses th text from column A in the same row, and use that as the sheetname i the formula. I also need to get the contents from cell F30 from eac corresponding sheet. I tried this in the first 4 rows of column B and it doesn't work obviously. Cell B1: =...

Re: Using sheetname variable in a formula
Hello. Suppose a cell (say A1) contains the name of a valid worksheet (sa Sheet1, Sheet2, and Sheet3). I would like to use the *contents* of cell A1 in my formula to get a data on that sheet. Something like: =*SheetNameStoredInCellA1*!A23 I know *=Sheet2!A23* is valid but when I try =A1!A23, that does not work Hope someone can help -- mm ----------------------------------------------------------------------- mms's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1653 View this thread: http://www.excelforum.com/showthread.php?threadid=31386 Hi look at the f...

Making formula read sheetname from a cell in my main sheet.
Hi, How do I ask this question ? Here's an attempt. I have a sheet called ADMIN that contains numerous buttons that run VB functions I have created. The sheet ADMIN has a cell (V4) that contains the name of the current worksheet that is being processed. I have another sheet called OUTPUT, and the OUTPUT cell C3 currently has the formula =ROUND(Newsagents!F9*$C$1,2). I want to know how to put the word Newsagents in cell ADMIN!V4 and then change the formula on the line above to read from admin V4. I know how to do it in VB, but on this occasion I need to do it in ...

SheetName (Application.Caller.Parent.Name) Forces Excel to Save File
I have a VBA function in Excel that I have been using for years that will display the sheet name in a cell. The VBA function is as follows: Function SheetName() As String Application.Volatile SheetName = Application.Caller.Parent.Name End Function This works great. However, I have found that if I am using this function if I open a worksheet and not change anything Excel will prompt me if I want to save the worksheet if I have not made any changes. Why is this happening and is there a way to prevent this from happening? Thanks, Mike The problem may be Application.Volitile If ...