new tab

Keith.Jackson

Board Regular
Joined
Mar 31, 2005
Messages
115
what would be a macro to create a new worksheet with the name of a name I have typed in a cell?
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

andyh69uk

New Member
Joined
Nov 23, 2005
Messages
31
Try this:-

Code:
ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Worksheets("Sheet1").Range("A1").Value

This will add a worksheet at the end and name it the value of A1 on Sheet1

Hopes this helps

Andy
 

shajueasow

Well-known Member
Joined
Oct 7, 2004
Messages
1,926
Try this one.

Sub newsheet()
Sheets.Add
ActiveSheet.Name = Sheets("Sheet1").Range("A1").Text
End Sub

Assumed that the name for the new sheet is given in Cell A1 of Sheet1.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
to check if the sheet already exists add some errorhandling
Code:
Sub create_sheet()
Dim ShName As String
Dim ShExists As Boolean

ShName = Sheets("Sheet1").Range("A1").Text

On Error Resume Next
ShExists = Len(Worksheets(ShName).Name) > 0
On Error GoTo 0

    If ShExists Then
    MsgBox "Worksheet already exists", 48, "Title"
    Else
    ActiveWorkbook.Worksheets.Add After:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = ShName
    End If
    
End Sub
kind regards,
Erik
 

Watch MrExcel Video

Forum statistics

Threads
1,118,069
Messages
5,570,010
Members
412,304
Latest member
citrus
Top