Name of Work Sheet to be placed in any cell

A

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
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top