Add Worksheet, Name it & Put Name in Cell

Magicman87 · Nov 27th 2004

Hi,

I am trying to write a program in vba that will create a new worksheet at the end of the workbook and name it to what i want. Then i require the name of the worksheet to be placed in a cell on a different page of the workbook. So far i have managed to create the new worksheet but cannot figure out how to tell the program to place the name of the new worksheet into a cell on a different page.

Barrie Davidson · Nov 28th 2004

Re: Worksheet Tabs

You could try something like:

Code

Sheets("Sheet3").Range("A1").Value = ActiveSheet.Name

This codes assumes you want to put the sheet name in A1 on sheet "Sheet3" and that the new sheet is the active sheet.

Regards,

**Jack in the UK** · Nov 28th 2004

Re: Worksheet Tabs

I have written this for you, take care that only a-z [A-Z] and 0-9 are used in cell A1 as tab names can get fussy and the code will bug also the datastream should not be toooo long suggest under 10 characters just in case.

Jack

Code

Option Explicit
Public wWkbook As Excel.Workbook
Public wSh3 As Excel.Worksheet
Public rTarget As Excel.Range
Public sTabName As String
Sub J_R_ExcelSolutions_TabsNameA()
' [b]Written by: Jack in the UK[/b]
' [url]www.excel-it.com[/url]
' [COLOR=DarkOrange]Excel 2003[/COLOR]
Set wWkbook = ThisWorkbook
Set wSh3 = wWkbook.Sheets(3)
Set rTarget = wSh3.Range("a1")
If rTarget <> vbNullString Then
With wSh3
.Name = rTarget.Text
End With
Else
'jiuk - some code here if needs be
Exit Sub
End If
theEnd:
Call J_R_ExcelSolutions_TabsNameA_ValKillA
'jiuk - more code
End Sub


Sub J_R_ExcelSolutions_TabsNameA_ValKillA()
Set wWkbook = Nothing
Set wSh3 = Nothing
Set rTarget = Nothing
End Sub

Display More

BTW my code is very locked and not as flexable as you might like, you shold be able to edit to your requirements as the code i hope is quite understandable and editable.

Magicman87 · Nov 28th 2004

Re: Worksheet Tabs

Quote from Barrie Davidson
You could try something like:
Code
Sheets("Sheet3").Range("A1").Value = ActiveSheet.Name
This codes assumes you want to put the sheet name in A1 on sheet "Sheet3" and that the new sheet is the active sheet.

Regards,
Display More

Thank you very much. That was exactly what i was looking for.

Very Kindest Regards

Magicman87 · Nov 28th 2004

Re: Worksheet Tabs

Quote from Jack in the UK
I have written this for you, take care that only a-z [A-Z] and 0-9 are used in cell A1 as tab names can get fussy and the code will bug also the datastream should not be toooo long suggest under 10 characters just in case.

Jack
Code
Option Explicit
Public wWkbook As Excel.Workbook
Public wSh3 As Excel.Worksheet
Public rTarget As Excel.Range
Public sTabName As String
Sub J_R_ExcelSolutions_TabsNameA()
' [b]Written by: Jack in the UK[/b]
' [url]www.excel-it.com[/url]
' [COLOR=DarkOrange]Excel 2003[/COLOR]
Set wWkbook = ThisWorkbook
Set wSh3 = wWkbook.Sheets(3)
Set rTarget = wSh3.Range("a1")
If rTarget <> vbNullString Then
With wSh3
.Name = rTarget.Text
End With
Else
'jiuk - some code here if needs be
Exit Sub
End If
theEnd:
Call J_R_ExcelSolutions_TabsNameA_ValKillA
'jiuk - more code
End Sub


Sub J_R_ExcelSolutions_TabsNameA_ValKillA()
Set wWkbook = Nothing
Set wSh3 = Nothing
Set rTarget = Nothing
End Sub
Display More
BTW my code is very locked and not as flexable as you might like, you shold be able to edit to your requirements as the code i hope is quite understandable and editable.

Thank you for taking the time to answer my question. Although this code works it is not what i was looking for.
Thank you again for your help.

Kindest Regards

Dave Hawley · Nov 28th 2004

Re: Worksheet Tabs

I see Barry has done this for you. The code below might still of some use though.

Code

Sub AddSheetAndName()
Dim wSheet As Worksheet


Set wSheet = Worksheets.Add(After:=Sheets(Sheets.Count))
    With wSheet
         .Name = "MyNewSheet"
         .Range("A1") = .Name
    End With
End Sub

reno · Dec 13th 2005

Re: Add Worksheet, Name it & Put Name in Cell

Quote from "VBA"

Set wSheet = Worksheets.Add(After:=Sheets(Sheets.Count))
With wSheet
.Name = "MyNewSheet"
.Range("A1") = .Name
End With

This is really usefull for what i am doing, but if for example i called a worksheet number 1 how can i get it so that as soon as the code add a worksheet it automatically calls it number 2, then adding another will call it number 3 and so on

Dave Hawley · Dec 13th 2005

Re: Add Worksheet, Name it & Put Name in Cell

HI reno

Welcome to ozgrid

Please start your own Thread if/when you have a question. If needed, link to this thread.

Add Worksheet, Name it & Put Name in Cell

Participate now!

Share