Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets changing name
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 .. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sheets changing names | Excel Programming | |||
Numbers changing between sheets | Excel Discussion (Misc queries) | |||
Changing dates on multiple sheets | New Users to Excel | |||
Changing Sheets in chart | Charts and Charting in Excel | |||
Changing the value in multiple sheets without selecting those sheets | Excel Programming |