Naming Sheet Tab as Todays Date

[redneckopen]

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!

 

[Von Pookie]

Try:

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]

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]

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.

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]

Thank you Kristy!

 

[redneckopen]

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]

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

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]

Running into a 'bug' with this line;

.PasteSpecial xlPasteColumnWidths 'paste column widths

?? any idea why??

 

[Von Pookie]

No idea at the moment, seeing as the code works for me when I run it in 2002.

What is the error displayed?

 

[redneckopen]

Run-time error '1004':

PasteSpecial method of Range class failed