Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
Following is my macro that copies a hidden sheet
(extended reservation) and renames the sheet as the value in cell C4 on Create Reservation sheet. If C4 is an existing sheet name the macro returns an error and the new sheet is named extended reseervatiom(2). I would like, if there is a duplicate, for extended reservation(2) not to be created. Also how do I assign short cutkey to the macro. Sorry so long and Thanks!!! ' ' create Macro ' Macro recorded 8/21/2003 by zz535a1 ' ' Sheets("EXTENDED RESERVATION").Visible = True Sheets("EXTENDED RESERVATION").Copy After:=Sheets(2) Range("C1:C7").Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("B9:B104").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Selection.Locked = True Selection.FormulaHidden = False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("EXTENDED RESERVATION").Select ActiveWindow.SelectedSheets.Visible = False Call Worksheet_Change End Sub Private Sub Worksheet_Change() Dim sName As String sName = CStr(Sheets("CREATE RESERVATION").Range ("C4").Formula) Sheets("EXTENDED RESERVATION (2)").Name = sName End Sub .. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
Private Sub Worksheet_Change()
Dim sName As String Dim ws as worksheet sName = CStr(Sheets("CREATE RESERVATION").Range ("C4").Formula) For Each ws In ActiveWorkbook.Worksheets if ws.Name=sName exit sub end if Next Sheets("EXTENDED RESERVATION (2)").Name = sName End Sub .. -----Original Message----- Following is my macro that copies a hidden sheet (extended reservation) and renames the sheet as the value in cell C4 on Create Reservation sheet. If C4 is an existing sheet name the macro returns an error and the new sheet is named extended reseervatiom(2). I would like, if there is a duplicate, for extended reservation(2) not to be created. Also how do I assign short cutkey to the macro. Sorry so long and Thanks!!! ' ' create Macro ' Macro recorded 8/21/2003 by zz535a1 ' ' Sheets("EXTENDED RESERVATION").Visible = True Sheets("EXTENDED RESERVATION").Copy After:=Sheets(2) Range("C1:C7").Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("B9:B104").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Selection.Locked = True Selection.FormulaHidden = False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("EXTENDED RESERVATION").Select ActiveWindow.SelectedSheets.Visible = False Call Worksheet_Change End Sub Private Sub Worksheet_Change() Dim sName As String sName = CStr(Sheets("CREATE RESERVATION").Range ("C4").Formula) Sheets("EXTENDED RESERVATION (2)").Name = sName End Sub .. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) |