Copy sheet
 

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

Copy sheet
 

You can try this, it works for Period 1, but how can we get information on
the current period No? I'll try to help, if you give some more details!

Sub CopySheet()
Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=Sheets(2)
Sheets("Sheet1 (2)").Select

Activesheet.Name ="Period 1"
Activesheet.Tab.ColorIndex = 35

Sheets("Sheet1").Select
End Sub

Regards,
Stefi

€žPaul€� ezt Ã*rta:

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

Copy sheet
 

Try code like the following:

Sub AAA()
Dim CurrWS As Worksheet
Dim NewWS As Workbook
Dim N As Long
Dim M As Long
Dim CurrName As String
Dim NewName As String

Set CurrWS = ActiveSheet
CurrName = CurrWS.Name
N = InStrRev(CurrName, " ")
If N = 0 Then
Exit Sub
End If
M = CLng(Mid(CurrName, N + 1))
M = M + 1
NewName = Left(CurrName, N) & CStr(M)
CurrWS.Copy after:=Worksheets(CurrName)
ActiveSheet.Name = NewName

End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Tue, 10 Mar 2009 01:03:51 -0700 (PDT), 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

Copy sheet
 

On 10 Mar., 16:35, Chip Pearson wrote:
Try code like the following:

Sub AAA()
* * Dim CurrWS As Worksheet
* * Dim NewWS As Workbook
* * Dim N As Long
* * Dim M As Long
* * Dim CurrName As String
* * Dim NewName As String

* * Set CurrWS = ActiveSheet
* * CurrName = CurrWS.Name
* * N = InStrRev(CurrName, " ")
* * If N = 0 Then
* * * * Exit Sub
* * End If
* * M = CLng(Mid(CurrName, N + 1))
* * M = M + 1
* * NewName = Left(CurrName, N) & CStr(M)
* * CurrWS.Copy after:=Worksheets(CurrName)
* * ActiveSheet.Name = NewName

End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
* * Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLCwww.cpearson.com
(email on web site)

On Tue, 10 Mar 2009 01:03:51 -0700 (PDT), Paul



wrote:
Hi, I have a timesheet(only onesheetin the workbook), at end of
each 4 week period users clicksnewmonth - this clearssheet, puts
newdates in and carries forward current hours (credit, debit).

I want to be able the workbook tosavea copy to anewsheet(within
same workbook), then clear times etc in mainsheet(Sheet1 only). I
have the following which works but want to rename thenewsheet
(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- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

This code looks very nice... It seems that the sheets generated will
be named current sheet +1? Is there any way to name it after the sheet
to the utmost right+1? E.g. if the rightmost sheet is named 36, the
nextsheet will be named 37? This macro if used on sheet 36 it wouldn't
be a problem... But what if I use the macro from a Main sheet(e.g. 1)
and wanted to generate a new sheet each time with an increasing
number, any suggetions for this?