Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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 |
#2
![]() |
|||
|
|||
![]()
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 |
#3
![]() |
|||
|
|||
![]()
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 |
#4
![]() |
|||
|
|||
![]()
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 |
#5
![]() |
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
how do i rename worksheet to equal cell name | Excel Worksheet Functions | |||
HOW DO I DELETE A WORKSHEET IN EXCEL? NOT HIDE IT OR RENAME IT | Excel Worksheet Functions | |||
Rename Excel Worksheet | Excel Discussion (Misc queries) | |||
rename an excel worksheet | Excel Discussion (Misc queries) | |||
Search/Match between 2 x separate Worksheets and populate result in third worksheet | Excel Discussion (Misc queries) |