Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets changing names
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. 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets changing names
You need to identify which is the latest sheet and copy the latest sheet instead of always copying the Master Sheet or copy the latest sheet to the Master sheet. Now how do you identify which is the latest sheet? You have a few choices. 1) Your code alway put the latest shet as the 2nd sheet in the workbook. so you can always copy the 2nd tab. this will not work if people move the tabs around in the workbook. 2) You can add to you macro code that will alphabetize the worksheet 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 letters in the message box in alphabetical order. 3) Get rid of the message box and automatcially add the sheet version to the each new sheet. I prefer to use number rather than letters like 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 Sub -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=159977 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets changing names
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" wrote: You need to identify which is the latest sheet and copy the latest sheet instead of always copying the Master Sheet or copy the latest sheet to the Master sheet. Now how do you identify which is the latest sheet? You have a few choices. 1) Your code alway put the latest shet as the 2nd sheet in the workbook. so you can always copy the 2nd tab. this will not work if people move the tabs around in the workbook. 2) You can add to you macro code that will alphabetize the worksheet 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 letters in the message box in alphabetical order. 3) Get rid of the message box and automatcially add the sheet version to the each new sheet. I prefer to use number rather than letters like 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 Sub -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=159977 Microsoft Office Help . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets changing names
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 -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=159977 Microsoft Office Help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Defined Names | Excel Worksheet Functions | |||
Changing directory names | Excel Programming | |||
Changing VB Component Names to match Worksheet names using VBE | Excel Programming | |||
changing 'names' | Excel Programming | |||
Changing the value in multiple sheets without selecting those sheets | Excel Programming |