Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy sheet and rename tab
Hi,
I have a time sheet that I use at work that I want to improve upon. We use excel 2003. There is a button that prints out the time sheet then clears the sheet ready for the next fortnight. What I want it to do is print it out, then copy the sheet into the same workbook and rename the tab. I have put a formula in cell A81 which is formatted so as to name the new tab. Below is the code that already exists to print out and reset the sheet. I tried recording a macro and entering the code but it wouldn't change the tab name. What code will I need to enter to copy the sheet and rename it. I am not a programmer so cannot do this myself. I would appreciate any help. Thankyou -- Kerry Sub newtsheet() Sheets("Timesheet").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1 Range("AS25").Select Selection.Copy Range("C17").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Range("AS29").Select Application.CutCopyMode = False Selection.Copy Range("B30").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Range( _ "C27:C28,F27:F28,I27:I28,L27:L28,O27:O28,R27:R28,U 27:U28,X27:X28,AA27:AA28,AD27:AD28,AG27:AG28,AJ27: AJ28,AM27:AM28,AP27:AP28,C18:C23,F18:F23,I18:I23,L 18:L23,O18:O23,X18:X23,AA18:AA23,AD18:AD23,AG18:AG 23,AJ18:AJ23" _ ).Select Range("AJ23").Activate ActiveWindow.LargeScroll Down:=-1 Union(Range( _ "AG11:AG14,AJ11:AJ14,AJ6:AJ9,AG6:AG9,AD6:AD9,AA6:A A9,X6:X9,O6:O9,L6:L9,I6:I9,F6:F9,C6:C9,C27:C28,F27 :F28,I27:I28,L27:L28,O27:O28,R27:R28,U27:U28,X27:X 28,AA27:AA28,AD27:AD28,AG27:AG28,AJ27:AJ28,AM27:AM 28,AP27:AP28,C18:C23,F18:F23,I18:I23,L18:L23,O18:O 23" _ ), Range( _ "X18:X23,AA18:AA23,AD18:AD23,AG18:AG23,AJ18:AJ23,C 11:C14,F11:F14,I11:I14,L11:L14,O11:O14,X11:X14,AA1 1:AA14,AD11:AD14,R6:R9,R11:R14,R18:R23,U6:U9,U11:U 14,U18:U23,AM6:AM9,AM11:AM14,AM18:AM23,AP6:AP9,AP1 1:AP14,AP18:AP23" _ )).Select Range("C6").Activate Application.CutCopyMode = False Selection.ClearContents Range("B5").Select Selection.Copy ActiveWindow.LargeScroll Down:=3 Range("A80").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Range("B5").Select ActiveCell.FormulaR1C1 = "=R[75]C[-1]+14" Range("b4").Select TitleText = InputBox( _ prompt:="Please enter fortnight number.", _ Default:="__") ActiveCell.FormulaR1C1 = TitleText Range("C6").Select End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to copy a sheet and rename it with the value of two cells from the source sheet? | Excel Programming | |||
Copy/Rename a sheet | Links and Linking in Excel | |||
Button to copy sheet, rename sheet sequencially. | Excel Programming | |||
Copy a sheet and rename it | Excel Programming | |||
copy Sheet and rename it! | Excel Programming |