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

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

--
Gary''s Student - gsnu200855


"Kerry" wrote:

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

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" wrote:

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

--
Gary''s Student - gsnu200855


"Kerry" wrote:

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

Make sure text wrapping is not killing you.

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


"Kerry" wrote:

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" wrote:

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

--
Gary''s Student - gsnu200855


"Kerry" wrote:

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

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.
--
Kerry


"Gary''s Student" wrote:

Make sure text wrapping is not killing you.

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


"Kerry" wrote:

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" wrote:

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

--
Gary''s Student - gsnu200855


"Kerry" wrote:

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