Create new sheet

[Damo10]

Hi,

I would like a macro to create a new sheet and give it the name from the text entered into a textbox in a user form and copy all the formats and formulas from sheet master (which is veryhidden) to the new sheet.

Regards Damian

 

[CharlesChuckieCharles]

Hi,

I would like a macro to create a new sheet and give it the name from the text entered into a textbox in a user form and copy all the formats and formulas from sheet master (which is veryhidden) to the new sheet.

Regards Damian

if you have a sheet that is veryhidden

then you can cope with

recording a macro copying one sheet to another then change the names accordingly

look up inputbox in vbA help


Helping you feed yourself

 

[Damo10]

I have written the following code which works, is there any way to stop invalid characters being entered for the sheet name?

Sub NewSheet()
Dim WSName As String
Dim ws As Worksheet
 
    WSName = InputBox("Enter new sheet name", "New Sheet")
    If WSName <> "" Then
    For Each ws In ActiveWorkbook.Worksheets
    If ws.Name = WSName Then GoTo NameError
    Next ws
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = WSName
    End If
    Exit Sub
 
NameError:
 MsgBox ("This sheet name already exists, please enter another name"), vbInformation, "Sheet Name Error"
End Sub

 

[CharlesChuckieCharles]

I have written the following code which works, is there any way to stop invalid characters being entered for the sheet name?

Sub NewSheet()
Dim WSName As String
Dim ws As Worksheet
 
    WSName = InputBox("Enter new sheet name", "New Sheet")
 
If WSName <> "" Then
    For Each ws In ActiveWorkbook.Worksheets
    If ws.Name = WSName Then GoTo NameError
    Next ws
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = WSName
    End If
    Exit Sub
 
NameError:
 MsgBox ("This sheet name already exists, please enter another name"), vbInformation, "Sheet Name Error"
End Sub

Edit


WSName = InputBox("Enter new sheet name", "New Sheet")
If WSName <> "" Then
For Each ws In ActiveWorkbook.Worksheets



to

WSName = InputBox("Enter new sheet name", "New Sheet")
WSName=ReplaceChars(WsName)
If WSName <> "" Then
For Each ws In ActiveWorkbook.Worksheets


call the following

'used to ensure valid Excel Sheet names
'
Function ReplaceChars(sstr As String) As String
Dim slen As Long
Dim soutput As String
Dim x As Long
Dim StringToReplace As String
 
'bad characters for Excel sheet names 1997-
StringToReplace = "\/?*[]!'."
slen = Len(StringToReplace)
For x = 1 To slen
    sstr = Replace(sstr, Mid$(StringToReplace, x, 1), "_")
Next x
 
 
ReplaceChars = sstr
End Function

This replaces invalid characters with an UnderScore
My personal choice

 

[Damo10]

Thanks that works great