Hi, I have a time sheet (only one sheet in the workbook), at end of
each 4 week period users clicks new month - this clears sheet, puts
new dates in and carries forward current hours (credit, debit).

I want to be able the workbook to save a copy to a new sheet (within
same workbook), then clear times etc in main sheet (Sheet1 only). I
have the following which works but want to rename the new sheet
(Sheet1 (2) ) to Period 1, then next month period 2 etc. Thanks


Sub CopySheet()
Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=Sheets(2)
Sheets("Sheet1 (2)").Select
ActiveWorkbook.Sheets("Sheet1 (2)").Tab.ColorIndex = 35
Sheets("Sheet1").Select
End Sub

Hi

Try this:

Sub CopySheet()
If Sheets(Sheets.Count).Name Like "Period*" Then
Period = Mid(Sheets(Sheets.Count).Name, 7)
Period = Period + 1
Else
Period = 1
End If
Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)
Sheets("Sheet1 (2)").Select
ActiveWorkbook.Sheets("Sheet1 (2)").Tab.ColorIndex = 35
Sheets(Sheets.Count).Name = "Period" & Period
Sheets("Sheet1").Select
End Sub

Regards,
Per

"Paul" skrev i meddelelsen
...
Hi, I have a time sheet (only one sheet in the workbook), at end of
each 4 week period users clicks new month - this clears sheet, puts
new dates in and carries forward current hours (credit, debit).

I want to be able the workbook to save a copy to a new sheet (within
same workbook), then clear times etc in main sheet (Sheet1 only). I
have the following which works but want to rename the new sheet
(Sheet1 (2) ) to Period 1, then next month period 2 etc. Thanks


Sub CopySheet()
Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=Sheets(2)
Sheets("Sheet1 (2)").Select
ActiveWorkbook.Sheets("Sheet1 (2)").Tab.ColorIndex = 35
Sheets("Sheet1").Select
End Sub

Hi,

I'm not sure where you want to put the sheet because if your workbook only
has one sheet I don't see how it can add the next sheet after sheet(2).
However, lets assume you want to add the new sheet directly after the sheet
you are on.

Sub CopySheet()
ActiveSheet.Copy After:=ActiveSheet
ActiveSheet.Tab.ColorIndex = 35
ActiveSheet.Name = "Period" & Sheets.Count - 1
End Sub

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Paul" wrote:

Hi, I have a time sheet (only one sheet in the workbook), at end of
each 4 week period users clicks new month - this clears sheet, puts
new dates in and carries forward current hours (credit, debit).

I want to be able the workbook to save a copy to a new sheet (within
same workbook), then clear times etc in main sheet (Sheet1 only). I
have the following which works but want to rename the new sheet
(Sheet1 (2) ) to Period 1, then next month period 2 etc. Thanks


Sub CopySheet()
Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=Sheets(2)
Sheets("Sheet1 (2)").Select
ActiveWorkbook.Sheets("Sheet1 (2)").Tab.ColorIndex = 35
Sheets("Sheet1").Select
End Sub

On Mar 10, 2:01*pm, "Per Jessen" wrote:
Hi

Try this:

Sub CopySheet()
* * If Sheets(Sheets.Count).Name Like "Period*" Then
* * * * Period = Mid(Sheets(Sheets.Count).Name, 7)
* * * * Period = Period + 1
* * Else
* * * * Period = 1
* * End If
* * Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)
* * Sheets("Sheet1 (2)").Select
* * ActiveWorkbook.Sheets("Sheet1 (2)").Tab.ColorIndex = 35
* * Sheets(Sheets.Count).Name = "Period" & Period
* * Sheets("Sheet1").Select
End Sub

Regards,
Per

"Paul" skrev i ...



Hi, I have a time sheet (only one sheet in the workbook), at end of
each 4 week period users clicks new month - this clears sheet, puts
new dates in and carries forward current hours (credit, debit).

I want to be able the workbook to save a copy to a new sheet (within
same workbook), then clear times etc in main sheet (Sheet1 only). I
have the following which works but want to rename the new sheet
(Sheet1 (2) ) to Period 1, then next month period 2 etc. Thanks

Sub CopySheet()
* *Sheets("Sheet1").Select
* *Sheets("Sheet1").Copy After:=Sheets(2)
* *Sheets("Sheet1 (2)").Select
* *ActiveWorkbook.Sheets("Sheet1 (2)").Tab.ColorIndex = 35
* *Sheets("Sheet1").Select
End Sub- Hide quoted text -

- Show quoted text -

Thank you very much, works well.