copy Sheet and rename it!

[John Smith]

I need to make a small VB program in Excel that can do the following:

- See weather a Specific Sheet name exists – then do nothing.
- If it do not exist then:
- Copy an existing Sheet.
- Give it a new name.

Please help

 

[Bernie Deitrick]

Sub JohnSmith()
Dim myName As String
Dim SpecName As String
Dim CopyName As String

CopyName = "Existing Sheet Name to Copy"
SpecName = "Specific Name to look for"

On Error GoTo MakeSheet
myName = Worksheets(SpecName).Name
Exit Sub

MakeSheet:
Worksheets(CopyName).Copy _
After:=Sheets(ActiveSheet.Index)
ActiveSheet.Name = SpecName

End Sub

HTH,
Bernie
MS Excel MVP

 

[John Smith]

I tried the following:

I want to call the subroutine from within the excel sheet somethin
like this:

=if(A23=””,””,JS(“SheetNameToCopy”,”NewSheetName”))

Or is there a better way to activate the subroutine when someone write
something in the cell “A23”??

if I place some information in cell A23 this returns “0”, but no ne
Sheet is created in the workbook.

I keyed in the following subroutine:
Sub JS(CName As String, SName As String)
Dim myName As String
'CName = "Existing Sheet Name to Copy"
'SName = "Specific Name to look for"
On Error GoTo MakeSheet
myName = Worksheets(SName).Name
Exit Sub
MakeSheet:
Worksheets(CName).Copy _
After:=Sheets(ActiveSheet.Index)
ActiveSheet.Name = SName
End Su

 

[Bernie Deitrick]

John,

You can't use a sub called as a function. You will need to use the
worksheet change event: Copy the code below, right click on the sheet tab
where you want this to happen, and select "View Code" , then paste the code
into the window that appears.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Address = "$A$23" Then
Application.EnableEvents = False
JS "SheetNameToCopy", "NewSheetName"
Application.EnableEvents = True
End If
End Sub