Copying sheet and generating name of new sheet

[Mikus]

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;WEEKDAY(A1;2)=6);"|";"")&TEXT(DAY(A1);"d")&IF(OR(WEEKDAY(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

 

[FSt1]

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,WEEKDAY(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,WEEKDAY(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

 

[Mike Fogleman]

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]

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!

 

[Mikus]

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

 

[Mikus]

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

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

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;WEEKDAY(A1;2)=6);"|";"")&TEXT(DAY(A1);"d")&IF(OR(WEEKDAY(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

 

[Mike Fogleman]

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 Before:=Sheets(2) 'Copies template
ActiveSheet.Range("A1").FormulaR1C1 = Date 'Adds todays day
ActiveSheet.Name = ActiveSheet.Range("M8") 'Names sheet

End Sub

You can adjust Before:=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

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

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

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;WEEKDAY(A1;2)=6);"|";"")&TEXT(DAY(A1);"d")&IF(OR(WEEKDAY(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

 

[Mikus]

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

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 Before:=Sheets(2) 'Copies template
ActiveSheet.Range("A1").FormulaR1C1 = Date 'Adds todays day
ActiveSheet.Name = ActiveSheet.Range("M8") 'Names sheet

End Sub

You can adjust Before:=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

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

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


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;WEEKDAY(A1;2)=6);"|";"")&TEXT(DAY(A1);"d")&IF(OR(WEEKDAY(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

 

[Mikus]

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

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

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 Before:=Sheets(2) 'Copies template
ActiveSheet.Range("A1").FormulaR1C1 = Date 'Adds todays day
ActiveSheet.Name = ActiveSheet.Range("M8") 'Names sheet

End Sub

You can adjust Before:=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

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

:

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


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;WEEKDAY(A1;2)=6);"|";"")&TEXT(DAY(A1);"d")&IF(OR(WEEKDAY(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

 

[Mike Fogleman]

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 Before:=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

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

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

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 Before:=Sheets(2) 'Copies template
ActiveSheet.Range("A1").FormulaR1C1 = Date 'Adds todays day
ActiveSheet.Name = ActiveSheet.Range("M8") 'Names sheet

End Sub

You can adjust Before:=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


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

:

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


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;WEEKDAY(A1;2)=6);"|";"")&TEXT(DAY(A1);"d")&IF(OR(WEEKDAY(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

 

[Mikus]

Thank you Mike, now it's flawless !

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 Before:=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

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

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

:

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 Before:=Sheets(2) 'Copies template
ActiveSheet.Range("A1").FormulaR1C1 = Date 'Adds todays day
ActiveSheet.Name = ActiveSheet.Range("M8") 'Names sheet

End Sub

You can adjust Before:=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


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

:

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


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;WEEKDAY(A1;2)=6);"|";"")&TEXT(DAY(A1);"d")&IF(OR(WEEKDAY(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