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 Masters 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 dont 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
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

oldjay
..

Hi Oldjay

I don't think you are being very clear (your words). Can you reword
your request? This line;

"On the next save to (12-4-09a) it actually has the list that was in
(12-4-09a) "

is a real head scratcher. If 12-04-09 is new how can it have the same
list that was on itself. You need to clarify your post as I suspect
what you want to do is straight forward.

I have put your code in a new workbook, renamed a sheet Master. The
code creates a new sheet called 12-04-09 with the same structure as
Master. On the second attempt it creates a new sheet with the same
structure as the Master sheet. Can you tell us what it is supposed to
look like?

Take care

Marcus

The code you have will always copy the Master worksheet because of the
following line.

Set ShtToCopy = Sheets("Master")

If you want it to copy the worksheet you are working on then you need to
copy the ActiveSheet. If you do the following it will fix the problem.

Copy the following code into a standard module. (To create a standard module
when in the VBA editor select Insert - Module.) Standard modules are named
Module1, Module2 etc.

The code is amended to copy the ActiveSheet plus automatically find the next
alpha character for the worksheet name if the name already exists. (No need
for the user to have to enter it.)

Sub CopyWorkSheet()

Dim wsShtToCopy As Worksheet
Dim strNewShtDate As String
Dim strNewShtName As String
Dim wsNewSht As Worksheet
Dim intChr As Integer


Set wsShtToCopy = ActiveSheet

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

'Copy the date name to another variable
strNewShtName = strNewShtDate

'Test for new sheet name already existing
'by attempting to assign to a variable
intChr = 65 'Initialize Chr for "A"
Do
On Error Resume Next
Set wsNewSht = Sheets(strNewShtName)

'If error is zero then worksheet exists
'therefore append an alpha character and test again
If Err.Number = 0 Then 'No Error
strNewShtName = strNewShtDate & Chr(intChr)
intChr = intChr + 1
Else
'Worksheet does not already exist so exit loop
Exit Do
End If
On Error GoTo 0 'Resume error trapping
Loop

On Error GoTo 0 'Resume normal error trapping

wsShtToCopy.Copy After:=Sheets(1)
ActiveSheet.Name = strNewShtName

End Sub

Now copy the following code into the Master Worksheet code area. Note that
the sub name must match the button name. To find the button name click Design
Mode button on the worksheet, right click the button and select properties.
The button name is the first line of the properties. (Don't forget to turn
off design mode after exiting from the properties.)

Private Sub CommandButton7_Click()
Call CopyWorkSheet
End Sub

--
Regards,

OssieMac

Like Marcus, I was also confused by your explanation. Because I wrote the
original code that you amended to include Inputbox for the appended alpha
character, I am now simply assuming that the ActiveSheet becomes the new
sheet that you want to copy and not the original Master.

Therefore if my assumption is incorrect, you do need to confirm if it is the
ActiveSheet you want to copy or the Master sheet to be copied each time.

--
Regards,

OssieMac

Sorry for the confusion. As an old 84 year old fart I can't code like I did
in the Eighties. The code does what I want except that it over writes the
Master sheet to be the same a the new saved sheet

"OssieMac" wrote:

Like Marcus, I was also confused by your explanation. Because I wrote the
original code that you amended to include Inputbox for the appended alpha
character, I am now simply assuming that the ActiveSheet becomes the new
sheet that you want to copy and not the original Master.

Therefore if my assumption is incorrect, you do need to confirm if it is the
ActiveSheet you want to copy or the Master sheet to be copied each time.

--
Regards,

OssieMac

I am not sure what you are doing. The code you first posted will always copy
the Master sheet. The code I have given you will always copy the active sheet
and the Master sheet is not overwritten.

I have assumed that what you want to do is:-

Start with the Master sheet and you make a copy of it.

You then amend the copy and make another new copy.

You then amend the newest copy and make another copy and continue this way.

Is my assumption correct?

--
Regards,

OssieMac

The latest code you gave me does overwrite the Master sheet. Everything else
is OK

"OssieMac" wrote:

I am not sure what you are doing. The code you first posted will always copy
the Master sheet. The code I have given you will always copy the active sheet
and the Master sheet is not overwritten.

I have assumed that what you want to do is:-

Start with the Master sheet and you make a copy of it.

You then amend the copy and make another new copy.

You then amend the newest copy and make another copy and continue this way.

Is my assumption correct?

--
Regards,

OssieMac