Copy sheet and rename tab

K

Kerry

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,U27:U28,X27:X28,AA27:AA28,AD27:AD28,AG27:AG28,AJ27:AJ28,AM27:AM28,AP27:AP28,C18:C23,F18:F23,I18:I23,L18:L23,O18:O23,X18:X23,AA18:AA23,AD18:AD23,AG18:AG23,AJ18:AJ23" _
).Select
Range("AJ23").Activate
ActiveWindow.LargeScroll Down:=-1
Union(Range( _

"AG11:AG14,AJ11:AJ14,AJ6:AJ9,AG6:AG9,AD6:AD9,AA6:AA9,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:X28,AA27:AA28,AD27:AD28,AG27:AG28,AJ27:AJ28,AM27:AM28,AP27:AP28,C18:C23,F18:F23,I18:I23,L18:L23,O18:O23" _
), Range( _

"X18:X23,AA18:AA23,AD18:AD23,AG18:AG23,AJ18:AJ23,C11:C14,F11:F14,I11:I14,L11:L14,O11:O14,X11:X14,AA11:AA14,AD11:AD14,R6:R9,R11:R14,R18:R23,U6:U9,U11:U14,U18:U23,AM6:AM9,AM11:AM14,AM18:AM23,AP6:AP9,AP11: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
 
G

Gary''s Student

Sub MakeACopy()
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = Application.InputBox(prompt:="enter new sheet name",
Type:=2)
End Sub
 
K

Kerry

Hi Gary"s Student,
I entered the code but it said syntax error on the activesheet.name line. Is
it possible to enter this code directly into the existing code?
Thanks for your help.

Kerry


Gary''s Student said:
Sub MakeACopy()
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = Application.InputBox(prompt:="enter new sheet name",
Type:=2)
End Sub

--
Gary''s Student - gsnu200855


Kerry said:
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,U27:U28,X27:X28,AA27:AA28,AD27:AD28,AG27:AG28,AJ27:AJ28,AM27:AM28,AP27:AP28,C18:C23,F18:F23,I18:I23,L18:L23,O18:O23,X18:X23,AA18:AA23,AD18:AD23,AG18:AG23,AJ18:AJ23" _
).Select
Range("AJ23").Activate
ActiveWindow.LargeScroll Down:=-1
Union(Range( _

"AG11:AG14,AJ11:AJ14,AJ6:AJ9,AG6:AG9,AD6:AD9,AA6:AA9,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:X28,AA27:AA28,AD27:AD28,AG27:AG28,AJ27:AJ28,AM27:AM28,AP27:AP28,C18:C23,F18:F23,I18:I23,L18:L23,O18:O23" _
), Range( _

"X18:X23,AA18:AA23,AD18:AD23,AG18:AG23,AJ18:AJ23,C11:C14,F11:F14,I11:I14,L11:L14,O11:O14,X11:X14,AA11:AA14,AD11:AD14,R6:R9,R11:R14,R18:R23,U6:U9,U11:U14,U18:U23,AM6:AM9,AM11:AM14,AM18:AM23,AP6:AP9,AP11: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
Click to expand...
Click to expand...
 
G

Gary''s Student

Make sure text wrapping is not killing you.

Prompt:= and Type:= are all part of a single line!
--
Gary''s Student - gsnu200855


Kerry said:
Hi Gary"s Student,
I entered the code but it said syntax error on the activesheet.name line. Is
it possible to enter this code directly into the existing code?
Thanks for your help.

Kerry


Gary''s Student said:
Sub MakeACopy()
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = Application.InputBox(prompt:="enter new sheet name",
Type:=2)
End Sub

--
Gary''s Student - gsnu200855


Kerry said:
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,U27:U28,X27:X28,AA27:AA28,AD27:AD28,AG27:AG28,AJ27:AJ28,AM27:AM28,AP27:AP28,C18:C23,F18:F23,I18:I23,L18:L23,O18:O23,X18:X23,AA18:AA23,AD18:AD23,AG18:AG23,AJ18:AJ23" _
).Select
Range("AJ23").Activate
ActiveWindow.LargeScroll Down:=-1
Union(Range( _

"AG11:AG14,AJ11:AJ14,AJ6:AJ9,AG6:AG9,AD6:AD9,AA6:AA9,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:X28,AA27:AA28,AD27:AD28,AG27:AG28,AJ27:AJ28,AM27:AM28,AP27:AP28,C18:C23,F18:F23,I18:I23,L18:L23,O18:O23" _
), Range( _

"X18:X23,AA18:AA23,AD18:AD23,AG18:AG23,AJ18:AJ23,C11:C14,F11:F14,I11:I14,L11:L14,O11:O14,X11:X14,AA11:AA14,AD11:AD14,R6:R9,R11:R14,R18:R23,U6:U9,U11:U14,U18:U23,AM6:AM9,AM11:AM14,AM18:AM23,AP6:AP9,AP11: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
Click to expand...
Click to expand...
Click to expand...
 
K

Kerry

That works thankyou. That is one way of solving the copy and rename problem.
I will continue to work on the program so that when I press the new worksheet
button it will print it out then copy and rename automatically and clear the
old time sheet.

Thankyou very much for your help Gary"s Student.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Unhide sheet, copy and rename new sheets from list, rehide sheet 8

Top