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