Naming a worksheet with today's date

[richarddhill]

Hi all you peoiple with real skills,

I want to name a worksheet with todays date and have tried the following code only to get an error message saying "Object variable or With Block Variable not set". I am using Excel 2007

Dim WS As Worksheet

Sheets("Goods Received").Select
'and select the cells and copy them
Cells.Select
Selection.Copy
'then add a new worksheet

Sheets.Add After:=Sheets(Sheets.Count)
'and paste the values of the cell into here
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'then select cell A1 and make the cell value todays date
Range("A1").Select
ActiveCell.FormulaR1C1 = "=TODAY()"

WS.Name = WS.Range("A1").Text

Can anyone suggest an alternative that works? I would be most grateful.

Regards,


Richard

 

[Special-K99]

I'm no VBA expert but should that be

WS.Name = WS.Range("A1").Value

You might need a function to turn it into a text date somewhere

 

[Richard Schollar]

Hi Richard

Something like the following should work:

Dim ws As Worksheet

Set ws = Worksheets.Add

With Sheets("Goods Received")
  Union(.UsedRange,.Range("A1")).Copy
End With

ws.Range("A1").PasteSpecial xlPasteValues

ws.Name = Format(Date,"dd-mmm-yyyy")

ws.Move After:=Sheets(Sheets.Count)

 

[VoG]

Try

Dim WS As Worksheet
Sheets("Goods Received").Copy After:=Sheets(Sheets.Count)
Set WS = ActiveSheet
With WS
    With .UsedRange
        .Value = .Value
    End With
    .Name = Format(Date, "dd-mmm-yyyy")
End With

 

[richarddhill]

What can I say? Now it works. Thanks especially to the last two which both worked fine.

Richard