rename worksheet
 

Hi,
I have been given the code below which will enable a user to create a new
worksheet from a copy of one stored in my workbook.
What I require is once the new sheet has been created - rename it as todays
date.
ie user clicks on the 'create todays log' button, a copy of my master sheet
is shown and this newly created worksheet is now renamed after todays date.
hope you can help - and go easy as I'm new to this.
many thanks

Sub Create_log()
Sheets("Log master").Visible = True
Sheets("Log Master").Copy After:=ActiveSheet
'ActiveSheet.Name =
Sheets("Log Master").Visible = False
End Sub

Hi,

try this:

Sub Create_log()
Application.ScreenUpdating = False
With Sheets("Log master")
.Visible = True
.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Format(Date, "YYYY-MM-DD")
.Visible = False
End With
Application.ScreenUpdating = True
End Sub

Regards,
KL


"Anthony" wrote in message
...
Hi,
I have been given the code below which will enable a user to create a new
worksheet from a copy of one stored in my workbook.
What I require is once the new sheet has been created - rename it as
todays
date.
ie user clicks on the 'create todays log' button, a copy of my master
sheet
is shown and this newly created worksheet is now renamed after todays
date.
hope you can help - and go easy as I'm new to this.
many thanks

Sub Create_log()
Sheets("Log master").Visible = True
Sheets("Log Master").Copy After:=ActiveSheet
'ActiveSheet.Name =
Sheets("Log Master").Visible = False
End Sub

Great -it worked
can I ask another question
if a log for 'today' has already been created, what code do I (well you!)
need to add to advise the user of this, and a message box asking them if they
want to view it,then being taken there if required.,
thanks again

"KL" wrote:

Hi,

try this:

Sub Create_log()
Application.ScreenUpdating = False
With Sheets("Log master")
.Visible = True
.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Format(Date, "YYYY-MM-DD")
.Visible = False
End With
Application.ScreenUpdating = True
End Sub

Regards,
KL


"Anthony" wrote in message
...
Hi,
I have been given the code below which will enable a user to create a new
worksheet from a copy of one stored in my workbook.
What I require is once the new sheet has been created - rename it as
todays
date.
ie user clicks on the 'create todays log' button, a copy of my master
sheet
is shown and this newly created worksheet is now renamed after todays
date.
hope you can help - and go easy as I'm new to this.
many thanks

Sub Create_log()
Sheets("Log master").Visible = True
Sheets("Log Master").Copy After:=ActiveSheet
'ActiveSheet.Name =
Sheets("Log Master").Visible = False
End Sub

Sub Create_log()
Application.ScreenUpdating = False
With Sheets("Log master")
.Visible = True
On Error Resume Next
Set sh = Worksheets(Format(Date, "YYYY-MM-DD"))
On Error Goto 0
If Not sh Is Nothing Then
ans = Msgbox("Sheet already exists - view it?", vbYesNo)
If ans = vbYes Then
sh.ACtivate
End If
Else
.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Format(Date, "YYYY-MM-DD")
.Visible = False
End If
End With
Application.ScreenUpdating = True
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Anthony" wrote in message
...
Great -it worked
can I ask another question
if a log for 'today' has already been created, what code do I (well you!)
need to add to advise the user of this, and a message box asking them if
they
want to view it,then being taken there if required.,
thanks again

"KL" wrote:

Hi,

try this:

Sub Create_log()
Application.ScreenUpdating = False
With Sheets("Log master")
.Visible = True
.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Format(Date, "YYYY-MM-DD")
.Visible = False
End With
Application.ScreenUpdating = True
End Sub

Regards,
KL


"Anthony" wrote in message
...
Hi,
I have been given the code below which will enable a user to create a
new
worksheet from a copy of one stored in my workbook.
What I require is once the new sheet has been created - rename it as
todays
date.
ie user clicks on the 'create todays log' button, a copy of my master
sheet
is shown and this newly created worksheet is now renamed after todays
date.
hope you can help - and go easy as I'm new to this.
many thanks

Sub Create_log()
Sheets("Log master").Visible = True
Sheets("Log Master").Copy After:=ActiveSheet
'ActiveSheet.Name =
Sheets("Log Master").Visible = False
End Sub

Sub Create_log()
Sheets("Log master").Visible = True
Sheets("Log Master").Copy After:=ActiveSheet
ActiveSheet.Name = format(date,"yyyy_mm_dd")
Sheets("Log Master").Visible = False
End Sub

Remember that worksheet names can't include slashes (/).

(and the second time you run this the same day, it'll blow up!)


Anthony wrote:

Hi,
I have been given the code below which will enable a user to create a new
worksheet from a copy of one stored in my workbook.
What I require is once the new sheet has been created - rename it as todays
date.
ie user clicks on the 'create todays log' button, a copy of my master sheet
is shown and this newly created worksheet is now renamed after todays date.
hope you can help - and go easy as I'm new to this.
many thanks

Sub Create_log()
Sheets("Log master").Visible = True
Sheets("Log Master").Copy After:=ActiveSheet
'ActiveSheet.Name =
Sheets("Log Master").Visible = False
End Sub

--

Dave Peterson