Help with text function

[Albert Browne]

I am trying to copy a sheet to a different workbook and then rename the
sheet by adding a date from cell B3. I have seen the text() function below
used to get the date from a cell and convert it to text. When I try what is
shown below I get variable not defined. Only part of the macro is shown.
Cell B3 is formatted as Date. What am I doing wrong?



Sheets("SET UP").Select
Sheets("SET UP").Copy Before:=Workbooks("Archives.xls").Sheets(1)
Sheets("SET UP").Select
Sheets("SET UP").Name = "SET UP " & Text(B3, "dd - mm - yyyy")

Thanks,

Albert

 

[Rick Rothstein \(MVP - VB\)]

B3 is not a cell reference (to VBA), so it thinks it is probably a variable
name. Since you are using Option Explicit, VB is warning that you are using
a variable named B3 but haven't declared it. What you want to use is
Range("B3").Value, but Text is not a VB function, so another error will be
generated after you do that. The VBA function you want is Format (same
arguments at you are attempting to use in Text).

Rick

 

[Harald Staff]

Hi ALbert

Try
Sheets("SET UP").Name = "SET UP " & Range("B3".Text)
or
Sheets("SET UP").Name = "SET UP " & Format$(Range("B3").Value, "dd - mm -
yyyy")

HTH. Best wishes Harald

 

[Ken]

Albert

You need to change Text (which you have seen as a funtion) to Format,
which you will see works in VBA code.
Also, you need to elaborate on B3 in your code.

The following works for me:

Sub test2()
Sheets("SET UP").Select
Sheets("SET UP").Copy Before:=Workbooks("Book3").Sheets(1)
Sheets("SET UP").Select
Sheets("SET UP").Name = "SET UP" & Format(Range("B3"), "dd - mm -
yyyy")
End Sub

Good luck.

Ken
Norfolk, Va

 

[PCLIVE]

Maybe:

Sheets("SET UP").Name = "SET UP " &
WorksheetFunction.Text(Range("B3").Value, "dd - mm - yyyy")

HTH,
Paul