Prev: Disabling "cut" function - version 2003/2007
Next: Formula for counting frequency then sorting
From: Akhil Goel on 7 Apr 2010 08:49
I am having multiple worksheets in an Excel file and now I want to use the
name of each worksheet in a new worksheet in the same file.

Is there a formula which can copy the name of worksheet into a Cell of
another worksheet
From: Pete_UK on 7 Apr 2010 09:02
This article from Bob Phillips' site covers what you want:

http://www.xldynamic.com/source/xld.xlFAQ0002.html

Hope this helps.

Pete

On Apr 7, 1:49 pm, Akhil Goel <AkhilG...(a)discussions.microsoft.com>
wrote:
> I am having multiple worksheets in an Excel file and now I want to use the
> name of each worksheet in a new worksheet in the same file.
>
> Is there a formula which can copy the name of worksheet into a Cell of
> another worksheet

From: Bob Phillips on 7 Apr 2010 09:03

Worksheets(2).Range("A1").Value2 = Worksheets(1).Name

--

HTH

Bob

"Akhil Goel" <AkhilGoel(a)discussions.microsoft.com> wrote in message
news:31EDC621-5FD3-4A82-972B-CA9C1A7B12BB(a)microsoft.com...
>I am having multiple worksheets in an Excel file and now I want to use the
> name of each worksheet in a new worksheet in the same file.
>
> Is there a formula which can copy the name of worksheet into a Cell of
> another worksheet


From: Dave Peterson on 7 Apr 2010 09:12
Option Explicit
Sub testme()

Dim iCtr As Long
Dim rCtr As Long

rCtr = 0
For iCtr = 1 To Sheets.Count
If Worksheets(iCtr).Name = Worksheets("NewWorksheetname").Name Then
'skip it
Else
rCtr = rCtr + 1
With Worksheets("NewWorksheetname")
With .Cells(rCtr, "A")
.NumberFormat = "@" 'text
.Value = Sheets(iCtr).Name
End With
End With
End If
Next iCtr

End Sub



Akhil Goel wrote:
>
> I am having multiple worksheets in an Excel file and now I want to use the
> name of each worksheet in a new worksheet in the same file.
>
> Is there a formula which can copy the name of worksheet into a Cell of
> another worksheet

--

Dave Peterson
 | 
Pages: 1
Prev: Disabling "cut" function - version 2003/2007
Next: Formula for counting frequency then sorting