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