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 Befo=Workbooks("Archives.xls").Sheets(1)
Sheets("SET UP").Select
Sheets("SET UP").Name = "SET UP " & Text(B3, "dd - mm - yyyy")

Thanks,

Albert

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


"Albert Browne" wrote in message
...
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 Befo=Workbooks("Archives.xls").Sheets(1)
Sheets("SET UP").Select
Sheets("SET UP").Name = "SET UP " & Text(B3, "dd - mm - yyyy")

Thanks,

Albert

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

"Albert Browne" wrote in message
...
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 Befo=Workbooks("Archives.xls").Sheets(1)
Sheets("SET UP").Select
Sheets("SET UP").Name = "SET UP " & Text(B3, "dd - mm - yyyy")

Thanks,

Albert

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 Befo=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

On Jun 12, 2:55*pm, "Albert Browne"
wrote:
*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 Befo=Workbooks("Archives.xls").Sheets(1)
*Sheets("SET UP").Select
*Sheets("SET UP").Name = "SET UP " & Text(B3, "dd - mm - yyyy")

Thanks,

Albert

Maybe:

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

HTH,
Paul

--

"Albert Browne" wrote in message
...
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 Befo=Workbooks("Archives.xls").Sheets(1)
Sheets("SET UP").Select
Sheets("SET UP").Name = "SET UP " & Text(B3, "dd - mm - yyyy")

Thanks,

Albert