Name of Work Sheet to be placed in any cell

[Akash]

hi,

I am using a Code to generate a new Sheet:

Sub SchedSheets()

If MsgBox("Are you sure that you want to Create the List of Sheets
Mentioned in the Column A?", vbYesNo, "Are you sure?") = vbNo Then
Exit Sub
End If
Dim LastCell As Range, Rng As Range, cell As Range
Dim ws As Worksheet

Set ws = ActiveSheet
Set LastCell = ws.Cells(Rows.Count, "A").End(xlUp)
Set Rng = ws.Range("A1", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Sheets("1").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
Columns("A:A").Select
Selection.EntireColumn.Hidden = True
End If
Next
End Sub

When ever i enter any Name in Cell A1 (Suppose "Akash") and run the
macro it helps me by creating a new sheet with the name as Akash.

Now is this possible to reflect the Name of Work Sheet in any cell of
the Worksheet Named as "Akash".

Awaiting for your mail.

thanks

Akash

 

[Tom Ogilvy]

Sub SchedSheets()

If MsgBox("Are you sure that you want to Create the List of Sheets
Mentioned in the Column A?", vbYesNo, "Are you sure?") = vbNo Then
Exit Sub
End If
Dim LastCell As Range, Rng As Range, cell As Range
Dim ws As Worksheet

Set ws = ActiveSheet
Set LastCell = ws.Cells(Rows.Count, "A").End(xlUp)
Set Rng = ws.Range("A1", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Sheets("1").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
Activesheet.Range("C3").Value = Activesheet.Name
Columns("A:A").Select
Selection.EntireColumn.Hidden = True
End If
Next
End Sub

if you mean with a formula, look at the bottom of this page:

http://www.cpearson.com/excel/excelF.htm