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 .. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets changing name
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets changing name
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets changing name
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets changing name
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets changing name
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets changing name
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets changing name
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 -- Regards, OssieMac "oldjay" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets changing name
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" wrote: 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 -- Regards, OssieMac "oldjay" wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets changing name
Hi there again Oldjay,
Firstly when you use the code to disable alerts dont 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, dont forget to delete the code in the Master worksheet. -- Regards, OssieMac |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets changing name
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" wrote: Hi there again Oldjay, Firstly when you use the code to disable alerts dont 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, dont forget to delete the code in the Master worksheet. -- Regards, OssieMac |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets changing name
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) -- Regards, OssieMac |
Reply |
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 |