Put this code on a button either in the toolbars or on the Summary Sheet
to copy the latest worksheet. If you put the code on the Week sheet the
code gets created on every new sheet. then if you need to change the
code there will be up to 52 copies of the code.

I made this code very robust to handle a lot of different situations.
I don't know if the sheets are in order since people can switch the
order of the sheets. So the code looks for the latest sheet in the
format week X. Then creates a new sheet called week (X + 1).

I don't know enough about the data in each sheet or the what the
summary sheet looks like to write code to make the summary sheet.


Private Sub CommandButton1_Click()

'Add new sheet
WkNum = 0
For Each Sht In Sheets
'only look at sheets starting with Week
If Left(UCase(Sht.Name), 4) = "WEEK" Then
SheetName = Sht.Name
'make sure there is a space in the sheet name
If InStr(SheetName, " ") Then
'get the string after the last space
ShtNum = _
Trim(Mid(SheetName, InStrRev(SheetName, " ")))
'make sure it is a number
If IsNumeric(ShtNum) Then
ShtNum = Val(ShtNum)
'only save the highest week num
If ShtNum WkNum Then
WkNum = ShtNum
End If
End If
End If
End If
Next Sht

'test is a sheet with Week X was found
'and if so create new sheet
If WkNum < 0 Then
Set NewestSht = Sheets("Week " & WkNum)
'copy latest week worksheet and add copy to end of sheets
NewestSht.Copy after:=Sheets(Sheets.Count)
'name new sht wit correct week number
ActiveSheet.Name = "week " & (WkNum + 1)
End If


End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=166042

Microsoft Office Help