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 :) |
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 |