Sheets changing names

[oldjay]

The following macro saves various lists to new sheets in the same workbook.
The first save is named 12-4-09 the second is 12-4-09a and so on. The initial
Sheet is “Masterâ€
What happens is that on the first save the Master’s list does not remain as
it was originally but changes to match the list that was just saved as
(12-4-09) list This means that both sheets are the same On the next save to
(12-4-09a) it actually has the list that was in 12-4-09a and the new list is
in the old 12-4-09. I don’t know if I am making my self clear but if you open
a workbook and rename the sheet Master add a couple of words and run the
macro. Then add another word and run the macro you will see what I mean. I
want to save each sheet to the name I assigned
oldjay

Private Sub CommandButton7_Click() 'Save Worksheet

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

Set ShtToCopy = Sheets("Master")

'Assign proposed new worksheet name to variable
NewShtName = Format(Date, "mm-dd-yy")

'Test for new sheet name already existing
'by attempting to assign to a variable
On Error Resume Next
Set NewSht = Sheets(NewShtName)

'If error is zero then worksheet exists
If Err.Number = 0 Then 'No error
NewShtName = InputBox("Worksheet " & NewShtName _
& " already exists. Insert new sheet name by adding a letter to end of file
name ", "Cora's List", NewShtName)
On Error GoTo 0 'Resume error trapping ASAP
ShtToCopy.Copy After:=Sheets(1)
ActiveSheet.Name = NewShtName
Exit Sub
End If
'Exit Sub

On Error GoTo 0 'Resume error trapping ASAP
ShtToCopy.Copy After:=Sheets(1)
ActiveSheet.Name = NewShtName


End Sub

 

[joel]

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

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

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

2) You can add to you macro code that will alphabetize the workshee
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 letter
in the message box in alphabetical order.

3) Get rid of the message box and automatcially add the sheet versio
to the each new sheet. I prefer to use number rather than letters lik
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 Su

 

[oldjay]

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]

which website are you using. The microsoft website has been broken for
more than a month. It is not sending out emails to responses. All of
the microsoft postings are also displayed at the code cage. see this
website

http://tinyurl.com/y9acogq