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?