Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying sheet and generating name of new sheet
I need to create macro that would make a copy of sheet named "blank" and will
name this (copied) sheet like value specified in cell - M8 ! And before creating name for new sheet this macro should also put today's date (Now()) in cell A1 of new sheet I want to run this macro by keypress. For example Ctrl + F12 Now i will explain why i need this. Cell M8 will contain formula: =IF(ISBLANK(A1);"blank";IF(OR(WEEKDAY(A1;2)=7;WEEK DAY(A1;2)=6);"|";"")&TEXT(DAY(A1);"d")&IF(OR(WEEKD AY(A1;2)=7;WEEKDAY(A1;2)=6);"|";"")) This fomula read's value from cell A1 (as mentioned above cell A1 will contain today's date) If A1 will contain value "15.08.2005" (dd-mm-yyyy) then value of M8 will be 15 If A1 will contain value "14.08.2005" then value of M8 will be |14| In a word if A1 will contain weekend day then value in M8 will be number representing the day enclosed with lines, else it will put number representing the day without any lines Point of all this is that i need to create new tab for each day. And each sheet must contain today's date in A1 and sheet's name should represent that days number and if it's a weekend day than nummber will be easily identified by enclosing lines. And i want to do all this by keypress becouse i have to repeat this every day. So far i have code that would read value in cell M8 Private Sub Worksheet_Change(ByVal Target As Excel.Range) Const sNAMECELL As String = "M8" Const sERROR As String = "Invalid worksheet name in cell " Dim sSheetName As String With Target If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing Then sSheetName = Range(sNAMECELL).Value If Not sSheetName = "" Then On Error Resume Next Me.Name = sSheetName On Error GoTo 0 If Not sSheetName = Me.Name Then _ MsgBox sERROR & sNAMECELL End If End If End With End Sub But that's far from what i need, cuz this is only renaming tab name depending from value in cell M8 and works only if i open cell M8 for editing and hit enter Any ideas how do i acomplish this task ? It would be even more effective if i could identify weekend days by having different tab color instead of enclosing lines - for example red color. Any ideas on this are also welcome :) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying sheet and generating name of new sheet
Hi,
you have some serious problems here. 1. your formula doesn't work. at least not on xp in usa. this does work =IF(ISBLANK(A1),"blank",IF(OR(WEEKDAY(A1,2)=7,WEEK DAY(A1,2)=6),"|","")&TEXT(A1,"ddd")&IF(OR(WEEKDAY( A1,2)=7,WEEKDAY(A1,2)=6),"|","")) 2.if you plan to have more than 7 sheets, the macro will crash with that type of nameing conviction because in 7 days(sheets), the macro will try to name a sheet with a name that is already in use. you cant do that. 3. the code you have so far is a worksheet change event meaning it will only fire if something on the sheet changes like cell m8 which you have already noted. you say that you wanted to fire it from the keyboard short cut Cnt-F12. 4. keyboard shortcut cntl-F12 is already taken. see this site. http://www.cpearson.com/excel/KeyboardShortcuts.htm From what you wrote, i assume you have a template on sheet("Blank") and want to copy that sheet to a new sheet and name the sheet with a special name. Sub AddASheet() Sheets("Blank").Select 'selects template Range("A1").FormulaR1C1 = Date 'adds todays day Range("A1:M50").Copy 'copies template Sheets.Add 'Add new sheet ActiveSheet.Name = Sheets("Blank").Range("M8") 'Names sheet ActiveSheet.Paste 'Pastes template End Sub this will do that but you will have nameing convictions problems. i modified your formula =IF(ISBLANK(A1),"blank",IF(OR(WEEKDAY(A1,2)=7,WEEK DAY(A1,2)=6),"|","")&TEXT(A1,"ddd")&IF(OR(WEEKDAY( A1,2)=7,WEEKDAY(A1,2)=6),"|","")&" "&MONTH(A1)&(DAY(A1)&YEAR(A1))) this will give it a unique name. Adjust the copy range to fit your data. Post back if i totally misunderstood what you are trying to do. Regards FSt1 "Mikus" wrote: I need to create macro that would make a copy of sheet named "blank" and will name this (copied) sheet like value specified in cell - M8 ! And before creating name for new sheet this macro should also put today's date (Now()) in cell A1 of new sheet I want to run this macro by keypress. For example Ctrl + F12 Now i will explain why i need this. Cell M8 will contain formula: =IF(ISBLANK(A1);"blank";IF(OR(WEEKDAY(A1;2)=7;WEEK DAY(A1;2)=6);"|";"")&TEXT(DAY(A1);"d")&IF(OR(WEEKD AY(A1;2)=7;WEEKDAY(A1;2)=6);"|";"")) This fomula read's value from cell A1 (as mentioned above cell A1 will contain today's date) If A1 will contain value "15.08.2005" (dd-mm-yyyy) then value of M8 will be 15 If A1 will contain value "14.08.2005" then value of M8 will be |14| In a word if A1 will contain weekend day then value in M8 will be number representing the day enclosed with lines, else it will put number representing the day without any lines Point of all this is that i need to create new tab for each day. And each sheet must contain today's date in A1 and sheet's name should represent that days number and if it's a weekend day than nummber will be easily identified by enclosing lines. And i want to do all this by keypress becouse i have to repeat this every day. So far i have code that would read value in cell M8 Private Sub Worksheet_Change(ByVal Target As Excel.Range) Const sNAMECELL As String = "M8" Const sERROR As String = "Invalid worksheet name in cell " Dim sSheetName As String With Target If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing Then sSheetName = Range(sNAMECELL).Value If Not sSheetName = "" Then On Error Resume Next Me.Name = sSheetName On Error GoTo 0 If Not sSheetName = Me.Name Then _ MsgBox sERROR & sNAMECELL End If End If End With End Sub But that's far from what i need, cuz this is only renaming tab name depending from value in cell M8 and works only if i open cell M8 for editing and hit enter Any ideas how do i acomplish this task ? It would be even more effective if i could identify weekend days by having different tab color instead of enclosing lines - for example red color. Any ideas on this are also welcome :) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying sheet and generating name of new sheet
Question: Why are you creating a copy of sheet("blank")? Would a new sheet
that is added (which would be blank) be good, or is there something that you want copied, other than the date formula in M8? The formula can be evaluated in VB to name the new sheet, so the formula is not needed in the worksheet itself. Neither is today's date, unless you just want it in A1 as a reference on the sheet. Note on your formula: some Excel language versions use [;] instead of [,] for list separators. So your formula may be OK for your language. Since your formula uses the day of the month for naming the new sheet, you will get 28 to 31 new sheet names before a duplicate name occurs, not 7. At that point you would need to start a new workbook for the next month. Mike F "Mikus" wrote in message ... I need to create macro that would make a copy of sheet named "blank" and will name this (copied) sheet like value specified in cell - M8 ! And before creating name for new sheet this macro should also put today's date (Now()) in cell A1 of new sheet I want to run this macro by keypress. For example Ctrl + F12 Now i will explain why i need this. Cell M8 will contain formula: =IF(ISBLANK(A1);"blank";IF(OR(WEEKDAY(A1;2)=7;WEEK DAY(A1;2)=6);"|";"")&TEXT(DAY(A1);"d")&IF(OR(WEEKD AY(A1;2)=7;WEEKDAY(A1;2)=6);"|";"")) This fomula read's value from cell A1 (as mentioned above cell A1 will contain today's date) If A1 will contain value "15.08.2005" (dd-mm-yyyy) then value of M8 will be 15 If A1 will contain value "14.08.2005" then value of M8 will be |14| In a word if A1 will contain weekend day then value in M8 will be number representing the day enclosed with lines, else it will put number representing the day without any lines Point of all this is that i need to create new tab for each day. And each sheet must contain today's date in A1 and sheet's name should represent that days number and if it's a weekend day than nummber will be easily identified by enclosing lines. And i want to do all this by keypress becouse i have to repeat this every day. So far i have code that would read value in cell M8 Private Sub Worksheet_Change(ByVal Target As Excel.Range) Const sNAMECELL As String = "M8" Const sERROR As String = "Invalid worksheet name in cell " Dim sSheetName As String With Target If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing Then sSheetName = Range(sNAMECELL).Value If Not sSheetName = "" Then On Error Resume Next Me.Name = sSheetName On Error GoTo 0 If Not sSheetName = Me.Name Then _ MsgBox sERROR & sNAMECELL End If End If End With End Sub But that's far from what i need, cuz this is only renaming tab name depending from value in cell M8 and works only if i open cell M8 for editing and hit enter Any ideas how do i acomplish this task ? It would be even more effective if i could identify weekend days by having different tab color instead of enclosing lines - for example red color. Any ideas on this are also welcome :) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying sheet and generating name of new sheet
Hi FSt1
Thanks for your time and here are my reply to your post: 1) I don't have any problems with formula i wrote. It works fine. As Mike Fogelman (in later post) noticed i am using ; instead of , and it is becouse i live in Latvia and ; is our default list seperator :) 2) Mike Fogelman (in later post) has also noticed macro will work fine and there will be no error cuz i need sheet numbers from 1 to 31 (or 28) and they will represent days of the month not days of the week. I will have new workbook for each month. 3) Yes, you are right sure i need somethin else - that code was just an example of what i am using now. My first experience with VBA in excel really :) 4) Thanks for information, i named it ctrl + n Ok. now the macro it self Thanks for macro code ... that was almost what i need. I modified it little bit so it now looks like this Sub Add_sheet() Sheets("Blank").Select 'Selects template Range("A1:Q200").Copy 'Copies template Sheets.Add 'Add new sheet ActiveSheet.Paste 'Pastes template ActiveSheet.Range("A1").FormulaR1C1 = Date 'Adds todays day ActiveSheet.Name = ActiveSheet.Range("M8") 'Names sheet End Sub This is what i wanted, but can i only copy particular range from sheet "blank "? My original idea was to copy whole sheet (with all options and all formatings... i wanted macro that would reproduce copy sheet operation [right click on tab - move or copy - crate a c copy] ). I now have these problems (none of these problems occur if i manualu copy sheet "blank"): 1) New sheet doesn't have blank sheet options - for example don't show gridlines option 2) New sheet doeen't have column widths like they were set on blank sheet 3) I need to re-define (create new) some name definitions while copying, becouse new sheet contains #Ref errors. For example blank sheet has name definition: =blank!$H$3:$H$65536.... after copying new name should be created: "new generated name"!$H$3:$H$65536. For example =15!$H$3:$H$65536. This would avoid #Ref errors. 4) In "blank" sheet 2nd row is hidden in new sheet this row is un-hidden... I would like it to stay hidden If you like you can take a look at this excel file and evaluate these problems: http://www.svara-kontrole.lv/development.xls (try firing macro by pressing ctrl + n) If you have problems with list separator you can change it under regional settings! "FSt1" wrote: Hi, you have some serious problems here. 1. your formula doesn't work. at least not on xp in usa. this does work =IF(ISBLANK(A1),"blank",IF(OR(WEEKDAY(A1,2)=7,WEEK DAY(A1,2)=6),"|","")&TEXT(A1,"ddd")&IF(OR(WEEKDAY( A1,2)=7,WEEKDAY(A1,2)=6),"|","")) 2.if you plan to have more than 7 sheets, the macro will crash with that type of nameing conviction because in 7 days(sheets), the macro will try to name a sheet with a name that is already in use. you cant do that. 3. the code you have so far is a worksheet change event meaning it will only fire if something on the sheet changes like cell m8 which you have already noted. you say that you wanted to fire it from the keyboard short cut Cnt-F12. 4. keyboard shortcut cntl-F12 is already taken. see this site. http://www.cpearson.com/excel/KeyboardShortcuts.htm From what you wrote, i assume you have a template on sheet("Blank") and want to copy that sheet to a new sheet and name the sheet with a special name. Sub AddASheet() Sheets("Blank").Select 'selects template Range("A1").FormulaR1C1 = Date 'adds todays day Range("A1:M50").Copy 'copies template Sheets.Add 'Add new sheet ActiveSheet.Name = Sheets("Blank").Range("M8") 'Names sheet ActiveSheet.Paste 'Pastes template End Sub this will do that but you will have nameing convictions problems. i modified your formula =IF(ISBLANK(A1),"blank",IF(OR(WEEKDAY(A1,2)=7,WEEK DAY(A1,2)=6),"|","")&TEXT(A1,"ddd")&IF(OR(WEEKDAY( A1,2)=7,WEEKDAY(A1,2)=6),"|","")&" "&MONTH(A1)&(DAY(A1)&YEAR(A1))) this will give it a unique name. Adjust the copy range to fit your data. Post back if i totally misunderstood what you are trying to do. Regards FSt1 "Mikus" wrote: I need to create macro that would make a copy of sheet named "blank" and will name this (copied) sheet like value specified in cell - M8 ! And before creating name for new sheet this macro should also put today's date (Now()) in cell A1 of new sheet I want to run this macro by keypress. For example Ctrl + F12 Now i will explain why i need this. Cell M8 will contain formula: =IF(ISBLANK(A1);"blank";IF(OR(WEEKDAY(A1;2)=7;WEEK DAY(A1;2)=6);"|";"")&TEXT(DAY(A1);"d")&IF(OR(WEEKD AY(A1;2)=7;WEEKDAY(A1;2)=6);"|";"")) This fomula read's value from cell A1 (as mentioned above cell A1 will contain today's date) If A1 will contain value "15.08.2005" (dd-mm-yyyy) then value of M8 will be 15 If A1 will contain value "14.08.2005" then value of M8 will be |14| In a word if A1 will contain weekend day then value in M8 will be number representing the day enclosed with lines, else it will put number representing the day without any lines Point of all this is that i need to create new tab for each day. And each sheet must contain today's date in A1 and sheet's name should represent that days number and if it's a weekend day than nummber will be easily identified by enclosing lines. And i want to do all this by keypress becouse i have to repeat this every day. So far i have code that would read value in cell M8 Private Sub Worksheet_Change(ByVal Target As Excel.Range) Const sNAMECELL As String = "M8" Const sERROR As String = "Invalid worksheet name in cell " Dim sSheetName As String With Target If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing Then sSheetName = Range(sNAMECELL).Value If Not sSheetName = "" Then On Error Resume Next Me.Name = sSheetName On Error GoTo 0 If Not sSheetName = Me.Name Then _ MsgBox sERROR & sNAMECELL End If End If End With End Sub But that's far from what i need, cuz this is only renaming tab name depending from value in cell M8 and works only if i open cell M8 for editing and hit enter Any ideas how do i acomplish this task ? It would be even more effective if i could identify weekend days by having different tab color instead of enclosing lines - for example red color. Any ideas on this are also welcome :) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying sheet and generating name of new sheet
Hi FSt1
Thanks for your time and here are my reply to your post: 1) I don't have any problems with formula i wrote. It works fine. As Mike Fogelman (in later post) noticed i am using ; instead of , and it is becouse i live in Latvia and ; is our default list seperator :) 2) Mike Fogelman (in later post) has also noticed macro will work fine and there will be no error cuz i need sheet numbers from 1 to 31 (or 28) and they will represent days of the month not days of the week. I will have new workbook for each month. 3) Yes, you are right sure i need somethin else - that code was just an example of what i am using now. My first experience with VBA in excel really :) 4) Thanks for information, i named it ctrl + n Ok. now the macro it self Thanks for macro code ... that was almost what i need. I modified it little bit so it now looks like this Sub Add_sheet() Sheets("Blank").Select 'Selects template Range("A1:Q200").Copy 'Copies template Sheets.Add 'Add new sheet ActiveSheet.Paste 'Pastes template ActiveSheet.Range("A1").FormulaR1C1 = Date 'Adds todays day ActiveSheet.Name = ActiveSheet.Range("M8") 'Names sheet End Sub This is what i wanted, but can i only copy particular range from sheet "blank "? My original idea was to copy whole sheet (with all options and all formatings... i wanted macro that would reproduce copy sheet operation [right click on tab - move or copy - crate a c copy] ). I now have these problems (none of these problems occur if i manualu copy sheet "blank"): 1) New sheet doesn't have blank sheet options - for example don't show gridlines option 2) New sheet doeen't have column widths like they were set on blank sheet 3) I need to re-define (create new) some name definitions while copying, becouse new sheet contains #Ref errors. For example blank sheet has name definition: =blank!$H$3:$H$65536.... after copying new name should be created: "new generated name"!$H$3:$H$65536. For example =15!$H$3:$H$65536. This would avoid #Ref errors. 4) In "blank" sheet 2nd row is hidden in new sheet this row is un-hidden... I would like it to stay hidden |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying sheet and generating name of new sheet
Hi Mike
The idea is that sheet named "blank" is not empty ... it has formulas and formating and some other stuff in there ... consider it as a form. The scenario is simple - i come to job in the morning and hit ctrl + n - this action creates a copy of blank named 15 (consider today is 15th) then i can fill in infromation i need. I repeat this every day. When month is over i create new .xls file. I have one for each month. As you already noticed there wouldn't be any unique name problems. Actually i don't need formula that creates new tab name to appear in worksheet. I just don't know any other way how to do this. If this forumla could be under the macro code ... that would be perfectly well. How do i do this ? I still want todays date in cell A1 You understood me perfectly about naming sheets and [;] instead of [,] Please look at my reply to FSt1 post Thanks for your time and have a nice day "Mike Fogleman" wrote: Question: Why are you creating a copy of sheet("blank")? Would a new sheet that is added (which would be blank) be good, or is there something that you want copied, other than the date formula in M8? The formula can be evaluated in VB to name the new sheet, so the formula is not needed in the worksheet itself. Neither is today's date, unless you just want it in A1 as a reference on the sheet. Note on your formula: some Excel language versions use [;] instead of [,] for list separators. So your formula may be OK for your language. Since your formula uses the day of the month for naming the new sheet, you will get 28 to 31 new sheet names before a duplicate name occurs, not 7. At that point you would need to start a new workbook for the next month. Mike F "Mikus" wrote in message ... I need to create macro that would make a copy of sheet named "blank" and will name this (copied) sheet like value specified in cell - M8 ! And before creating name for new sheet this macro should also put today's date (Now()) in cell A1 of new sheet I want to run this macro by keypress. For example Ctrl + F12 Now i will explain why i need this. Cell M8 will contain formula: =IF(ISBLANK(A1);"blank";IF(OR(WEEKDAY(A1;2)=7;WEEK DAY(A1;2)=6);"|";"")&TEXT(DAY(A1);"d")&IF(OR(WEEKD AY(A1;2)=7;WEEKDAY(A1;2)=6);"|";"")) This fomula read's value from cell A1 (as mentioned above cell A1 will contain today's date) If A1 will contain value "15.08.2005" (dd-mm-yyyy) then value of M8 will be 15 If A1 will contain value "14.08.2005" then value of M8 will be |14| In a word if A1 will contain weekend day then value in M8 will be number representing the day enclosed with lines, else it will put number representing the day without any lines Point of all this is that i need to create new tab for each day. And each sheet must contain today's date in A1 and sheet's name should represent that days number and if it's a weekend day than nummber will be easily identified by enclosing lines. And i want to do all this by keypress becouse i have to repeat this every day. So far i have code that would read value in cell M8 Private Sub Worksheet_Change(ByVal Target As Excel.Range) Const sNAMECELL As String = "M8" Const sERROR As String = "Invalid worksheet name in cell " Dim sSheetName As String With Target If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing Then sSheetName = Range(sNAMECELL).Value If Not sSheetName = "" Then On Error Resume Next Me.Name = sSheetName On Error GoTo 0 If Not sSheetName = Me.Name Then _ MsgBox sERROR & sNAMECELL End If End If End With End Sub But that's far from what i need, cuz this is only renaming tab name depending from value in cell M8 and works only if i open cell M8 for editing and hit enter Any ideas how do i acomplish this task ? It would be even more effective if i could identify weekend days by having different tab color instead of enclosing lines - for example red color. Any ideas on this are also welcome :) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying sheet and generating name of new sheet
I have your worksheet and it seems fine the way it is. The problem with the
new sheet is that it is not a complete copy of "Blank" worksheet. References, formatting, sheet code, etc. are not copied. Use this to create a perfect copy with the name and date the way you need. Sub Add_sheet() Sheets("blank").Copy Befo=Sheets(2) 'Copies template ActiveSheet.Range("A1").FormulaR1C1 = Date 'Adds todays day ActiveSheet.Name = ActiveSheet.Range("M8") 'Names sheet End Sub You can adjust Befo=Sheets(2) to place the new sheet where you want. This will place each new sheet right after "Blank" and before yesterday's sheet. Mike F "Mikus" wrote in message ... Hi Mike The idea is that sheet named "blank" is not empty ... it has formulas and formating and some other stuff in there ... consider it as a form. The scenario is simple - i come to job in the morning and hit ctrl + n - this action creates a copy of blank named 15 (consider today is 15th) then i can fill in infromation i need. I repeat this every day. When month is over i create new .xls file. I have one for each month. As you already noticed there wouldn't be any unique name problems. Actually i don't need formula that creates new tab name to appear in worksheet. I just don't know any other way how to do this. If this forumla could be under the macro code ... that would be perfectly well. How do i do this ? I still want todays date in cell A1 You understood me perfectly about naming sheets and [;] instead of [,] Please look at my reply to FSt1 post Thanks for your time and have a nice day "Mike Fogleman" wrote: Question: Why are you creating a copy of sheet("blank")? Would a new sheet that is added (which would be blank) be good, or is there something that you want copied, other than the date formula in M8? The formula can be evaluated in VB to name the new sheet, so the formula is not needed in the worksheet itself. Neither is today's date, unless you just want it in A1 as a reference on the sheet. Note on your formula: some Excel language versions use [;] instead of [,] for list separators. So your formula may be OK for your language. Since your formula uses the day of the month for naming the new sheet, you will get 28 to 31 new sheet names before a duplicate name occurs, not 7. At that point you would need to start a new workbook for the next month. Mike F "Mikus" wrote in message ... I need to create macro that would make a copy of sheet named "blank" and will name this (copied) sheet like value specified in cell - M8 ! And before creating name for new sheet this macro should also put today's date (Now()) in cell A1 of new sheet I want to run this macro by keypress. For example Ctrl + F12 Now i will explain why i need this. Cell M8 will contain formula: =IF(ISBLANK(A1);"blank";IF(OR(WEEKDAY(A1;2)=7;WEEK DAY(A1;2)=6);"|";"")&TEXT(DAY(A1);"d")&IF(OR(WEEKD AY(A1;2)=7;WEEKDAY(A1;2)=6);"|";"")) This fomula read's value from cell A1 (as mentioned above cell A1 will contain today's date) If A1 will contain value "15.08.2005" (dd-mm-yyyy) then value of M8 will be 15 If A1 will contain value "14.08.2005" then value of M8 will be |14| In a word if A1 will contain weekend day then value in M8 will be number representing the day enclosed with lines, else it will put number representing the day without any lines Point of all this is that i need to create new tab for each day. And each sheet must contain today's date in A1 and sheet's name should represent that days number and if it's a weekend day than nummber will be easily identified by enclosing lines. And i want to do all this by keypress becouse i have to repeat this every day. So far i have code that would read value in cell M8 Private Sub Worksheet_Change(ByVal Target As Excel.Range) Const sNAMECELL As String = "M8" Const sERROR As String = "Invalid worksheet name in cell " Dim sSheetName As String With Target If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing Then sSheetName = Range(sNAMECELL).Value If Not sSheetName = "" Then On Error Resume Next Me.Name = sSheetName On Error GoTo 0 If Not sSheetName = Me.Name Then _ MsgBox sERROR & sNAMECELL End If End If End With End Sub But that's far from what i need, cuz this is only renaming tab name depending from value in cell M8 and works only if i open cell M8 for editing and hit enter Any ideas how do i acomplish this task ? It would be even more effective if i could identify weekend days by having different tab color instead of enclosing lines - for example red color. Any ideas on this are also welcome :) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying sheet and generating name of new sheet
Thank you! I modified it just a little bit.
Sub Add_sheet() Sheets("blank").Select Sheets("blank").Copy After:=Sheets(1) ActiveSheet.Range("A1").FormulaR1C1 = Date ActiveSheet.Name = ActiveSheet.Range("tab_name") End Sub Now it is perfectly what i wanted! How do i add messege box that tell's "You already created today's sheet" instead of runtime error ? e.g. If name to be created is already taken throw message and rollback to previous state "Mike Fogleman" wrote: I have your worksheet and it seems fine the way it is. The problem with the new sheet is that it is not a complete copy of "Blank" worksheet. References, formatting, sheet code, etc. are not copied. Use this to create a perfect copy with the name and date the way you need. Sub Add_sheet() Sheets("blank").Copy Befo=Sheets(2) 'Copies template ActiveSheet.Range("A1").FormulaR1C1 = Date 'Adds todays day ActiveSheet.Name = ActiveSheet.Range("M8") 'Names sheet End Sub You can adjust Befo=Sheets(2) to place the new sheet where you want. This will place each new sheet right after "Blank" and before yesterday's sheet. Mike F "Mikus" wrote in message ... Hi Mike The idea is that sheet named "blank" is not empty ... it has formulas and formating and some other stuff in there ... consider it as a form. The scenario is simple - i come to job in the morning and hit ctrl + n - this action creates a copy of blank named 15 (consider today is 15th) then i can fill in infromation i need. I repeat this every day. When month is over i create new .xls file. I have one for each month. As you already noticed there wouldn't be any unique name problems. Actually i don't need formula that creates new tab name to appear in worksheet. I just don't know any other way how to do this. If this forumla could be under the macro code ... that would be perfectly well. How do i do this ? I still want todays date in cell A1 You understood me perfectly about naming sheets and [;] instead of [,] Please look at my reply to FSt1 post Thanks for your time and have a nice day "Mike Fogleman" wrote: Question: Why are you creating a copy of sheet("blank")? Would a new sheet that is added (which would be blank) be good, or is there something that you want copied, other than the date formula in M8? The formula can be evaluated in VB to name the new sheet, so the formula is not needed in the worksheet itself. Neither is today's date, unless you just want it in A1 as a reference on the sheet. Note on your formula: some Excel language versions use [;] instead of [,] for list separators. So your formula may be OK for your language. Since your formula uses the day of the month for naming the new sheet, you will get 28 to 31 new sheet names before a duplicate name occurs, not 7. At that point you would need to start a new workbook for the next month. Mike F "Mikus" wrote in message ... I need to create macro that would make a copy of sheet named "blank" and will name this (copied) sheet like value specified in cell - M8 ! And before creating name for new sheet this macro should also put today's date (Now()) in cell A1 of new sheet I want to run this macro by keypress. For example Ctrl + F12 Now i will explain why i need this. Cell M8 will contain formula: =IF(ISBLANK(A1);"blank";IF(OR(WEEKDAY(A1;2)=7;WEEK DAY(A1;2)=6);"|";"")&TEXT(DAY(A1);"d")&IF(OR(WEEKD AY(A1;2)=7;WEEKDAY(A1;2)=6);"|";"")) This fomula read's value from cell A1 (as mentioned above cell A1 will contain today's date) If A1 will contain value "15.08.2005" (dd-mm-yyyy) then value of M8 will be 15 If A1 will contain value "14.08.2005" then value of M8 will be |14| In a word if A1 will contain weekend day then value in M8 will be number representing the day enclosed with lines, else it will put number representing the day without any lines Point of all this is that i need to create new tab for each day. And each sheet must contain today's date in A1 and sheet's name should represent that days number and if it's a weekend day than nummber will be easily identified by enclosing lines. And i want to do all this by keypress becouse i have to repeat this every day. So far i have code that would read value in cell M8 Private Sub Worksheet_Change(ByVal Target As Excel.Range) Const sNAMECELL As String = "M8" Const sERROR As String = "Invalid worksheet name in cell " Dim sSheetName As String With Target If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing Then sSheetName = Range(sNAMECELL).Value If Not sSheetName = "" Then On Error Resume Next Me.Name = sSheetName On Error GoTo 0 If Not sSheetName = Me.Name Then _ MsgBox sERROR & sNAMECELL End If End If End With End Sub But that's far from what i need, cuz this is only renaming tab name depending from value in cell M8 and works only if i open cell M8 for editing and hit enter Any ideas how do i acomplish this task ? It would be even more effective if i could identify weekend days by having different tab color instead of enclosing lines - for example red color. Any ideas on this are also welcome :) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying sheet and generating name of new sheet
I realized that it is not perfect... how do i modife code so that macros copy
new sheet exactly 1 place to the left from "blank". Todays dade should always be left from "blank" e.g If have tabs: 12 - |13| - |14| - 15 - blank and today is 16th then copy is created between 15 and blank, then next day it is 18th copy is created between 17 and blank In the end of the month it should look like 1 - 2 - 3 - ... - 29 - 30 - 31 - blank "Mikus" wrote: Thank you! I modified it just a little bit. Sub Add_sheet() Sheets("blank").Select Sheets("blank").Copy After:=Sheets(1) ActiveSheet.Range("A1").FormulaR1C1 = Date ActiveSheet.Name = ActiveSheet.Range("tab_name") End Sub Now it is perfectly what i wanted! How do i add messege box that tell's "You already created today's sheet" instead of runtime error ? e.g. If name to be created is already taken throw message and rollback to previous state "Mike Fogleman" wrote: I have your worksheet and it seems fine the way it is. The problem with the new sheet is that it is not a complete copy of "Blank" worksheet. References, formatting, sheet code, etc. are not copied. Use this to create a perfect copy with the name and date the way you need. Sub Add_sheet() Sheets("blank").Copy Befo=Sheets(2) 'Copies template ActiveSheet.Range("A1").FormulaR1C1 = Date 'Adds todays day ActiveSheet.Name = ActiveSheet.Range("M8") 'Names sheet End Sub You can adjust Befo=Sheets(2) to place the new sheet where you want. This will place each new sheet right after "Blank" and before yesterday's sheet. Mike F "Mikus" wrote in message ... Hi Mike The idea is that sheet named "blank" is not empty ... it has formulas and formating and some other stuff in there ... consider it as a form. The scenario is simple - i come to job in the morning and hit ctrl + n - this action creates a copy of blank named 15 (consider today is 15th) then i can fill in infromation i need. I repeat this every day. When month is over i create new .xls file. I have one for each month. As you already noticed there wouldn't be any unique name problems. Actually i don't need formula that creates new tab name to appear in worksheet. I just don't know any other way how to do this. If this forumla could be under the macro code ... that would be perfectly well. How do i do this ? I still want todays date in cell A1 You understood me perfectly about naming sheets and [;] instead of [,] Please look at my reply to FSt1 post Thanks for your time and have a nice day "Mike Fogleman" wrote: Question: Why are you creating a copy of sheet("blank")? Would a new sheet that is added (which would be blank) be good, or is there something that you want copied, other than the date formula in M8? The formula can be evaluated in VB to name the new sheet, so the formula is not needed in the worksheet itself. Neither is today's date, unless you just want it in A1 as a reference on the sheet. Note on your formula: some Excel language versions use [;] instead of [,] for list separators. So your formula may be OK for your language. Since your formula uses the day of the month for naming the new sheet, you will get 28 to 31 new sheet names before a duplicate name occurs, not 7. At that point you would need to start a new workbook for the next month. Mike F "Mikus" wrote in message ... I need to create macro that would make a copy of sheet named "blank" and will name this (copied) sheet like value specified in cell - M8 ! And before creating name for new sheet this macro should also put today's date (Now()) in cell A1 of new sheet I want to run this macro by keypress. For example Ctrl + F12 Now i will explain why i need this. Cell M8 will contain formula: =IF(ISBLANK(A1);"blank";IF(OR(WEEKDAY(A1;2)=7;WEEK DAY(A1;2)=6);"|";"")&TEXT(DAY(A1);"d")&IF(OR(WEEKD AY(A1;2)=7;WEEKDAY(A1;2)=6);"|";"")) This fomula read's value from cell A1 (as mentioned above cell A1 will contain today's date) If A1 will contain value "15.08.2005" (dd-mm-yyyy) then value of M8 will be 15 If A1 will contain value "14.08.2005" then value of M8 will be |14| In a word if A1 will contain weekend day then value in M8 will be number representing the day enclosed with lines, else it will put number representing the day without any lines Point of all this is that i need to create new tab for each day. And each sheet must contain today's date in A1 and sheet's name should represent that days number and if it's a weekend day than nummber will be easily identified by enclosing lines. And i want to do all this by keypress becouse i have to repeat this every day. So far i have code that would read value in cell M8 Private Sub Worksheet_Change(ByVal Target As Excel.Range) Const sNAMECELL As String = "M8" Const sERROR As String = "Invalid worksheet name in cell " Dim sSheetName As String With Target If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing Then sSheetName = Range(sNAMECELL).Value If Not sSheetName = "" Then On Error Resume Next Me.Name = sSheetName On Error GoTo 0 If Not sSheetName = Me.Name Then _ MsgBox sERROR & sNAMECELL End If End If End With End Sub But that's far from what i need, cuz this is only renaming tab name depending from value in cell M8 and works only if i open cell M8 for editing and hit enter Any ideas how do i acomplish this task ? It would be even more effective if i could identify weekend days by having different tab color instead of enclosing lines - for example red color. Any ideas on this are also welcome :) |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying sheet and generating name of new sheet
Mikus: This should fix the new sheet placement before "blank" and message if
sheet already exists and deletes the new sheet. Function SheetExists(SName As String, _ Optional ByVal WB As Workbook) As Boolean 'Chip Pearson On Error Resume Next If WB Is Nothing Then Set WB = ThisWorkbook SheetExists = CBool(Len(WB.Sheets(SName).Name)) End Function Sub Add_sheet() Dim NewName As String Sheets("blank").Select Sheets("blank").Copy Befo=Worksheets("blank") ActiveSheet.Range("A1").FormulaR1C1 = Date NewName = ActiveSheet.Range("tab_name") If SheetExists(NewName) = True Then Application.DisplayAlerts = False MsgBox ("You have already created today's sheet!") ActiveSheet.Delete Application.DisplayAlerts = True Else ActiveSheet.Name = ActiveSheet.Range("tab_name") End If End Sub I hope this is to your satisfaction. I think I covered all you asked for. Mike F "Mikus" wrote in message ... I realized that it is not perfect... how do i modife code so that macros copy new sheet exactly 1 place to the left from "blank". Todays dade should always be left from "blank" e.g If have tabs: 12 - |13| - |14| - 15 - blank and today is 16th then copy is created between 15 and blank, then next day it is 18th copy is created between 17 and blank In the end of the month it should look like 1 - 2 - 3 - ... - 29 - 30 - 31 - blank "Mikus" wrote: Thank you! I modified it just a little bit. Sub Add_sheet() Sheets("blank").Select Sheets("blank").Copy After:=Sheets(1) ActiveSheet.Range("A1").FormulaR1C1 = Date ActiveSheet.Name = ActiveSheet.Range("tab_name") End Sub Now it is perfectly what i wanted! How do i add messege box that tell's "You already created today's sheet" instead of runtime error ? e.g. If name to be created is already taken throw message and rollback to previous state "Mike Fogleman" wrote: I have your worksheet and it seems fine the way it is. The problem with the new sheet is that it is not a complete copy of "Blank" worksheet. References, formatting, sheet code, etc. are not copied. Use this to create a perfect copy with the name and date the way you need. Sub Add_sheet() Sheets("blank").Copy Befo=Sheets(2) 'Copies template ActiveSheet.Range("A1").FormulaR1C1 = Date 'Adds todays day ActiveSheet.Name = ActiveSheet.Range("M8") 'Names sheet End Sub You can adjust Befo=Sheets(2) to place the new sheet where you want. This will place each new sheet right after "Blank" and before yesterday's sheet. Mike F "Mikus" wrote in message ... Hi Mike The idea is that sheet named "blank" is not empty ... it has formulas and formating and some other stuff in there ... consider it as a form. The scenario is simple - i come to job in the morning and hit ctrl + n - this action creates a copy of blank named 15 (consider today is 15th) then i can fill in infromation i need. I repeat this every day. When month is over i create new .xls file. I have one for each month. As you already noticed there wouldn't be any unique name problems. Actually i don't need formula that creates new tab name to appear in worksheet. I just don't know any other way how to do this. If this forumla could be under the macro code ... that would be perfectly well. How do i do this ? I still want todays date in cell A1 You understood me perfectly about naming sheets and [;] instead of [,] Please look at my reply to FSt1 post Thanks for your time and have a nice day "Mike Fogleman" wrote: Question: Why are you creating a copy of sheet("blank")? Would a new sheet that is added (which would be blank) be good, or is there something that you want copied, other than the date formula in M8? The formula can be evaluated in VB to name the new sheet, so the formula is not needed in the worksheet itself. Neither is today's date, unless you just want it in A1 as a reference on the sheet. Note on your formula: some Excel language versions use [;] instead of [,] for list separators. So your formula may be OK for your language. Since your formula uses the day of the month for naming the new sheet, you will get 28 to 31 new sheet names before a duplicate name occurs, not 7. At that point you would need to start a new workbook for the next month. Mike F "Mikus" wrote in message ... I need to create macro that would make a copy of sheet named "blank" and will name this (copied) sheet like value specified in cell - M8 ! And before creating name for new sheet this macro should also put today's date (Now()) in cell A1 of new sheet I want to run this macro by keypress. For example Ctrl + F12 Now i will explain why i need this. Cell M8 will contain formula: =IF(ISBLANK(A1);"blank";IF(OR(WEEKDAY(A1;2)=7;WEEK DAY(A1;2)=6);"|";"")&TEXT(DAY(A1);"d")&IF(OR(WEEKD AY(A1;2)=7;WEEKDAY(A1;2)=6);"|";"")) This fomula read's value from cell A1 (as mentioned above cell A1 will contain today's date) If A1 will contain value "15.08.2005" (dd-mm-yyyy) then value of M8 will be 15 If A1 will contain value "14.08.2005" then value of M8 will be |14| In a word if A1 will contain weekend day then value in M8 will be number representing the day enclosed with lines, else it will put number representing the day without any lines Point of all this is that i need to create new tab for each day. And each sheet must contain today's date in A1 and sheet's name should represent that days number and if it's a weekend day than nummber will be easily identified by enclosing lines. And i want to do all this by keypress becouse i have to repeat this every day. So far i have code that would read value in cell M8 Private Sub Worksheet_Change(ByVal Target As Excel.Range) Const sNAMECELL As String = "M8" Const sERROR As String = "Invalid worksheet name in cell " Dim sSheetName As String With Target If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing Then sSheetName = Range(sNAMECELL).Value If Not sSheetName = "" Then On Error Resume Next Me.Name = sSheetName On Error GoTo 0 If Not sSheetName = Me.Name Then _ MsgBox sERROR & sNAMECELL End If End If End With End Sub But that's far from what i need, cuz this is only renaming tab name depending from value in cell M8 and works only if i open cell M8 for editing and hit enter Any ideas how do i acomplish this task ? It would be even more effective if i could identify weekend days by having different tab color instead of enclosing lines - for example red color. Any ideas on this are also welcome :) |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying sheet and generating name of new sheet
Thank you Mike, now it's flawless :)!
"Mike Fogleman" wrote: Mikus: This should fix the new sheet placement before "blank" and message if sheet already exists and deletes the new sheet. Function SheetExists(SName As String, _ Optional ByVal WB As Workbook) As Boolean 'Chip Pearson On Error Resume Next If WB Is Nothing Then Set WB = ThisWorkbook SheetExists = CBool(Len(WB.Sheets(SName).Name)) End Function Sub Add_sheet() Dim NewName As String Sheets("blank").Select Sheets("blank").Copy Befo=Worksheets("blank") ActiveSheet.Range("A1").FormulaR1C1 = Date NewName = ActiveSheet.Range("tab_name") If SheetExists(NewName) = True Then Application.DisplayAlerts = False MsgBox ("You have already created today's sheet!") ActiveSheet.Delete Application.DisplayAlerts = True Else ActiveSheet.Name = ActiveSheet.Range("tab_name") End If End Sub I hope this is to your satisfaction. I think I covered all you asked for. Mike F "Mikus" wrote in message ... I realized that it is not perfect... how do i modife code so that macros copy new sheet exactly 1 place to the left from "blank". Todays dade should always be left from "blank" e.g If have tabs: 12 - |13| - |14| - 15 - blank and today is 16th then copy is created between 15 and blank, then next day it is 18th copy is created between 17 and blank In the end of the month it should look like 1 - 2 - 3 - ... - 29 - 30 - 31 - blank "Mikus" wrote: Thank you! I modified it just a little bit. Sub Add_sheet() Sheets("blank").Select Sheets("blank").Copy After:=Sheets(1) ActiveSheet.Range("A1").FormulaR1C1 = Date ActiveSheet.Name = ActiveSheet.Range("tab_name") End Sub Now it is perfectly what i wanted! How do i add messege box that tell's "You already created today's sheet" instead of runtime error ? e.g. If name to be created is already taken throw message and rollback to previous state "Mike Fogleman" wrote: I have your worksheet and it seems fine the way it is. The problem with the new sheet is that it is not a complete copy of "Blank" worksheet. References, formatting, sheet code, etc. are not copied. Use this to create a perfect copy with the name and date the way you need. Sub Add_sheet() Sheets("blank").Copy Befo=Sheets(2) 'Copies template ActiveSheet.Range("A1").FormulaR1C1 = Date 'Adds todays day ActiveSheet.Name = ActiveSheet.Range("M8") 'Names sheet End Sub You can adjust Befo=Sheets(2) to place the new sheet where you want. This will place each new sheet right after "Blank" and before yesterday's sheet. Mike F "Mikus" wrote in message ... Hi Mike The idea is that sheet named "blank" is not empty ... it has formulas and formating and some other stuff in there ... consider it as a form. The scenario is simple - i come to job in the morning and hit ctrl + n - this action creates a copy of blank named 15 (consider today is 15th) then i can fill in infromation i need. I repeat this every day. When month is over i create new .xls file. I have one for each month. As you already noticed there wouldn't be any unique name problems. Actually i don't need formula that creates new tab name to appear in worksheet. I just don't know any other way how to do this. If this forumla could be under the macro code ... that would be perfectly well. How do i do this ? I still want todays date in cell A1 You understood me perfectly about naming sheets and [;] instead of [,] Please look at my reply to FSt1 post Thanks for your time and have a nice day "Mike Fogleman" wrote: Question: Why are you creating a copy of sheet("blank")? Would a new sheet that is added (which would be blank) be good, or is there something that you want copied, other than the date formula in M8? The formula can be evaluated in VB to name the new sheet, so the formula is not needed in the worksheet itself. Neither is today's date, unless you just want it in A1 as a reference on the sheet. Note on your formula: some Excel language versions use [;] instead of [,] for list separators. So your formula may be OK for your language. Since your formula uses the day of the month for naming the new sheet, you will get 28 to 31 new sheet names before a duplicate name occurs, not 7. At that point you would need to start a new workbook for the next month. Mike F "Mikus" wrote in message ... I need to create macro that would make a copy of sheet named "blank" and will name this (copied) sheet like value specified in cell - M8 ! And before creating name for new sheet this macro should also put today's date (Now()) in cell A1 of new sheet I want to run this macro by keypress. For example Ctrl + F12 Now i will explain why i need this. Cell M8 will contain formula: =IF(ISBLANK(A1);"blank";IF(OR(WEEKDAY(A1;2)=7;WEEK DAY(A1;2)=6);"|";"")&TEXT(DAY(A1);"d")&IF(OR(WEEKD AY(A1;2)=7;WEEKDAY(A1;2)=6);"|";"")) This fomula read's value from cell A1 (as mentioned above cell A1 will contain today's date) If A1 will contain value "15.08.2005" (dd-mm-yyyy) then value of M8 will be 15 If A1 will contain value "14.08.2005" then value of M8 will be |14| In a word if A1 will contain weekend day then value in M8 will be number representing the day enclosed with lines, else it will put number representing the day without any lines Point of all this is that i need to create new tab for each day. And each sheet must contain today's date in A1 and sheet's name should represent that days number and if it's a weekend day than nummber will be easily identified by enclosing lines. And i want to do all this by keypress becouse i have to repeat this every day. So far i have code that would read value in cell M8 Private Sub Worksheet_Change(ByVal Target As Excel.Range) Const sNAMECELL As String = "M8" Const sERROR As String = "Invalid worksheet name in cell " Dim sSheetName As String With Target If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing Then sSheetName = Range(sNAMECELL).Value If Not sSheetName = "" Then On Error Resume Next Me.Name = sSheetName On Error GoTo 0 If Not sSheetName = Me.Name Then _ MsgBox sERROR & sNAMECELL End If End If End With End Sub But that's far from what i need, cuz this is only renaming tab name depending from value in cell M8 and works only if i open cell M8 for editing and hit enter Any ideas how do i acomplish this task ? It would be even more effective if i could identify weekend days by having different tab color instead of enclosing lines - for example red color. Any ideas on this are also welcome :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help Taking alot data from one sheet (if not blank) and copying toa list on another sheet. | Excel Worksheet Functions | |||
Copying the repeated data of the previous sheet to the next sheet | Excel Discussion (Misc queries) | |||
Dynamic column chart - copying from Sheet to Sheet. | Excel Discussion (Misc queries) | |||
Dynamic column chart - copying from Sheet to Sheet. | Charts and Charting in Excel | |||
2 questions, copying data from sheet to sheet and assigning macro | Excel Worksheet Functions |