Joel - This post had a lot more replies than shown. Do you know how I can
retrieve them? I also can never get a notified of a reply and can't search
for my posts (I only get one I posted last year

oldjay.

"joel" wrote:


You need to identify which is the latest sheet and copy the latest sheet
instead of always copying the Master Sheet or copy the latest sheet to
the Master sheet.

Now how do you identify which is the latest sheet? You have a few
choices.

1) Your code alway put the latest shet as the 2nd sheet in the
workbook. so you can always copy the 2nd tab. this will not work if
people move the tabs around in the workbook.

2) You can add to you macro code that will alphabetize the worksheet
names and then sort the sheet names to find the last name alphabetical.
this will only work if the people using the workbook enters the letters
in the message box in alphabetical order.

3) Get rid of the message box and automatcially add the sheet version
to the each new sheet. I prefer to use number rather than letters like
excel does in parethesis


This is the code I would use

Private Sub CommandButton7_Click() 'Save Worksheet

Dim ShtToCopy As Worksheet
Dim NewShtName As String
Dim NewSht As Worksheet


'Assign proposed new worksheet name to variable
NewShtName = Format(Date, "mm-dd-yy")
LengthName = Len(NewShtName)
'find latest version of worksheet
VersionNumber = 0
Set ShtToCopy = Nothing
For Each Sht In Sheets
If Left(Sht.Name, LengthName) = NewShtName Then
'Test if there is a version number in parethesis
If InStr(Sht.Name, "(") Then
'remove number from parenthsis
NewVersionNumber = Mid(Sht.Name, InStr(Sht.Name, "("))
NewVersionNumber = Val(NewVersionNumber)
If NewVersionNumber VersionNumber Then
VersionNumber = NewVersionNumber
Set ShtToCopy = Sht
End If
Else
Set ShtToCopy = Sht
End If
End If

Next Sht

If ShtToCopy Is Nothing Then
Sheets("Master").Copy After:=Sheets(1)
ActiveSheet.Name = NewShtName
Else
ShtToCopy.Copy After:=Sheets(1)
End If
'Exit Sub


End Sub


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

Microsoft Office Help

.