Naming Sheet Tab as Todays Date

redneckopen

Board Regular
Joined
Nov 2, 2004
Messages
224
I would like to name each new sheet as todays date and not after a particular cell. This is what I have now;

Application.ScreenUpdating = False
Sheets("TimeList").Select
Sheets("TimeList").Copy After:=Sheets(3)
Sheets("TimeList").Select
Range("a6:g30").Select
Selection.ClearContents
Range("B6").Select
Sheets("TimeList (2)").Select
ActiveSheet.Name = Range("B3")
Sheets("TimeList").Select
Range("a6").Select
Application.ScreenUpdating = True

Thank you in advance!
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Try:

Code:
Application.ScreenUpdating = False
With Sheets("TimeList")
     .Copy After:=Sheets(3)
     .Range("A6:G30").ClearContents
End With
Sheets("TimeList (2)").Name = Format(Date, "mm-dd-yy")
Application.ScreenUpdating = True
 

redneckopen

Board Regular
Joined
Nov 2, 2004
Messages
224
That seems to work except that it doesn't save the formula results just the formulas. I do have some formulas that are on a separate sheet. Is there some code to change the formulas to the cell values before saving to a new sheet?? Thanks.
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
You could just copy the cells and paste values. Assuming you want to keep the formulas on the original sheet, just copy/paste on the *new* sheet.

Code:
Application.ScreenUpdating = False
With Sheets("TimeList")
     .Copy After:=Sheets(3)
     .Range("A6:G30").ClearContents
End With
With Sheets("TimeList (2)")
     .Cells.Copy
     .Cells.PasteSpecial xlPasteValues
     .Name = Format(Date, "mm-dd-yy")
End With
Application.ScreenUpdating = True
 

redneckopen

Board Regular
Joined
Nov 2, 2004
Messages
224

ADVERTISEMENT

Thank you Kristy!
 

redneckopen

Board Regular
Joined
Nov 2, 2004
Messages
224
I have references on a hidden sheet that when the new sheet is made the formulas must not be able to find them. Even copying doesn't work. I think I need to change them to thier values like asap utilities does. Got me, any help would be great!
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686

ADVERTISEMENT

How about create a new sheet then just copy the values onto that? Not copying the sheet itself, just the values from it:

Code:
Dim wsNew As Worksheet

Application.ScreenUpdating = False

'add new worksheet
Set wsNew = Sheets.Add(after:=Sheets(3))

'copy the cells on the original sheet
Sheets("TimeList").Cells.Copy
With wsNew
    'paste to the new sheet
    With .Cells
        .PasteSpecial xlPasteValues 'paste values
        .PasteSpecial xlPasteFormats 'paste cell formats
        .PasteSpecial xlPasteColumnWidths 'paste column widths
    End With
    .Range("A6:G30").ClearContents
    .Name = Format(Date, "mm-dd-yy")
End With

Application.ScreenUpdating = True
 

redneckopen

Board Regular
Joined
Nov 2, 2004
Messages
224
Running into a 'bug' with this line;

.PasteSpecial xlPasteColumnWidths 'paste column widths

?? any idea why??
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
No idea at the moment, seeing as the code works for me when I run it in 2002.

What is the error displayed?
 

Watch MrExcel Video

Forum statistics

Threads
1,111,910
Messages
5,541,533
Members
410,547
Latest member
htran4
Top