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]

Did you switch any of the lines around or add something inbetween the xlPasteFormats and xlPasteColumnWidths lines?

That's the only way I can (currently) reproduce the error. For example, if I use this:

With .Cells
        .PasteSpecial xlPasteValues 'paste values
        .PasteSpecial xlPasteFormats 'paste cell formats
        .Range("A6:G30").ClearContents
        .PasteSpecial xlPasteColumnWidths 'paste column widths
    End With

Where it is clearing the contents of A6:G30 before pasting the column widths, it causes that error because the copied cells are no longer on the clipboard because of the ClearContents command.

It's the same thing that would happen if you do it manually. You can copy a cell and paste it in different places, but if you perform an action after you copy the cells (say, entering a new value in a different cell) what you copied is no longer on the clipboard (the moving dotted line is gone).

 

[redneckopen]

FYI, I got this to work;


Sub update_sheet()

Dim wsNew As Worksheet

Application.ScreenUpdating = False
'add new worksheet
Set wsNew = Sheets.Add(After:=Sheets(3))

Sheets("TimeList").Select

'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

End With
Range("a6").Select
.Name = Format(Date, "mm-dd-yy")

End With
Sheets("TimeList").Select
Range("a6:g30").Select
Selection.ClearContents
Sheets("TimeList").Select
Range("a6").Select
Application.ScreenUpdating = True


End Sub


Thank you for your help!