O
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
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