Help with text function

  • Thread starter Thread starter Albert Browne
  • Start date Start date
A

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

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
 
Maybe:

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

HTH,
Paul
 
Back
Top