Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy Sheet and rename it!
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy Sheet and rename it!
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 " wrote in message ... 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! --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy Sheet and rename it!
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 -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy Sheet and rename it!
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 "John Smith " wrote in message ... I tried the following: I want to call the subroutine from within the excel sheet something like this: =if(A23="","",JS("SheetNameToCopy","NewSheetName") ) Or is there a better way to activate the subroutine when someone writes something in the cell "A23"?? if I place some information in cell A23 this returns "0", but no new 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 Sub --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i rename a sheet if the rename tab is inactive? | Excel Worksheet Functions | |||
keyboard shortcut to pull up sheet menu(copy,delete,rename,etc)? | Excel Discussion (Misc queries) | |||
Copy worksheet and rename sheet | Excel Discussion (Misc queries) | |||
Macro REQ: Automatically copy a template sheet and rename | Excel Worksheet Functions | |||
Copy/Rename a sheet | Links and Linking in Excel |