Sheets changing name

[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
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
..

 

[marcus]

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

 

[OssieMac]

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

 

[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.

 

[oldjay]

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]

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?

 

[oldjay]

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

 

[OssieMac]

What version of Excel ar you using? I have tested the code in Excel 2002 and
Excel 2007 and it does not overwrite the Master sheet.

I wonder if somehow the Master worksheet is remaining selected along with
the copy of the new worksheet that is created. If that occurs then both
sheets get updated. Check the worksheet tabs and ensure that only the most
recent sheet is selected.

If this is occurring then insert the following line as the last line of the
code after the line "ActiveSheet.Name = strNewShtName".

Sheets(strNewShtName).Select

 

[oldjay]

Excel 2000, 2002 and 2003
You have fixed all my existing problems. Thank you! I just found another
one. When I run the following macro I get an automation error. If I run it
again I get no error.

Private Sub CommandButton5_Click() 'Start new list
Dim ws As Worksheet
Dim lw As Integer
Dim MyConstant As Integer
Dim Counter As Integer
Dim rRange As Range
Dim rCell As Range

Application.DisplayAlerts = False
For Each ws In Sheets
If ws.Name <> "Master" Then ws.Delete
Next

Range("A2:I31").Select
Selection.ClearContents
Selection.Interior.ColorIndex = xlNone

Set rRange = Range("A2:I31")
lw = Range("A" & Rows.Count).End(xlUp).Row
MyConstant = Application.CountA(Sheets("Master").Range("A2:I31"))
Range("K23").Value = MyConstant

For Each rCell In rRange
If rCell.Interior.ColorIndex = 36 Then
Counter = 1 + Counter
End If
Next rCell
Range("K22").Value = Counter
Range("A2").Select

End Sub

 

[OssieMac]

Hi there again Oldjay,

Firstly when you use the code to disable alerts don’t forget to turn the
alerts back on at the end of the sub with the following.
Application.DisplayAlerts = True

The automation error is caused by deleting the worksheet that has the code
in it. When you are copying the worksheet, both the command button and the
code that goes with it are copied into the new worksheet and in fact you will
have multiple copies of the code; one copy in each worksheet. If you run the
code from a worksheet that is being deleted, the code associated with it gets
deleted also. If you run it from the master worksheet then you will not get
the error.

Having said that, there is a better way of arranging your code and calling
that code when you are copying worksheets. I know I previously said to put
the main code in a standard module and just call that code with the code
associated with a command button. However, now that you have introduced
deleting worksheets, I think the following method is preferable.

Instead of using the ActiveX command buttons from the Control Toolbox
toolbar, use the Button from the Forms toolbar. This way all of your code
goes into a standard module and you can name the subs whatever you like and
after creating the button from the Forms toolbar, you will get a dialog box
to assign a macro to it. (Later if you need to get back to the dialog box,
right click the button). Then when you copy a worksheet, only the button gets
copied and no additional copies of the code are made. The button on every
worksheet calls the same code in the standard module. You can run the delete
sheet code from any worksheet because the code never gets deleted.

If you use my suggested method, don’t forget to delete the code in the
Master worksheet.

 

[oldjay]

Thanks NO more Automation errors!
I can't the code to copy the Values and Formats with this line

Cells(NewRow, NewColumn).Value = Cells(X, Col).Value

Sub Move_Words()
Dim NewRow As Long
Dim NewColumn As Long
Dim Col As Long
Dim X As Long

NewRow = 1
'Find first unused column
NewColumn = Cells(1, Columns.Count) _
..End(xlToLeft).Offset(0, 1).Column

If NewColumn < 16 Then 'Ensure first run of code 'will start at column 16
NewColumn = 16
End If

For Col = 1 To 9
For X = 2 To 31
If Cells(X, Col) <> "" Then
Cells(NewRow, NewColumn).Value = Cells(X, Col).Value
NewRow = NewRow + 1
End If
Next X
Next Col

End Sub

 

[OssieMac]

Hi again oldjay,

If you want both values and formats you need to copy the source and paste it
to the destination. The following line of code copies and pastes in the one
line of code.

Cells(X, Col).Copy Destination:=Cells(NewRow, NewColumn)

The code can actually be shortened to the following because Destination is
default parameter.

Cells(X, Col).Copy Cells(NewRow, NewColumn)