How to convert a date to string via a macro

[Dobo_Bobo]

Hi, I've searched but I can't find any guidance specific to converting a date to string. I am still a noob teaching myself VBA code, so I'm really stuck. Please can you help?

As part of a large macro, I have a section that saves an opened file that needs (for the file name) the latest Sunday date each the macro is run. This part of the macro look at a cell to obtain the required date. This macro works but only if the cell has the date as a string.

The cell (I2) containing the date has the formula: Today()-1

(I run the macro every Moday using data from the previous day, thus I need to go back on day.)

To save risk of human error I need to run another macro that copies the date from I2 and pastes the date as a string in H2.

I recorded a macro where I copied from I2 > pasted the date into a text box > copied the date in the text box > selected G2 > right clicked selected PastSpecial > Paste: As: Text

I've removed the code that is not required, what I am left with is this:

Sub PasteDateAsString()

Range("I2").Copy
Range("H2").Select
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False

End Sub

When I run the macro I get this error:

"Run-Time error '1004':
PasteSpecial method of worksheet class failed"

The debug highlights the last line of the code "ActiveSheet.Paste......"

I just don't know enough about VBA to know why the macro won't work. I've searched via Google but I can't find any solutions. If anyone has a solution I will really appreciate it and I will return to give thanks because you guys are vital to me being able to learn VBA.

 

[steve the fish]

Im not quite sure why your other macro would need a text date but try this:

Range("H2").Value = Format(Date - 1, "@")

 

[footoo]

I have a section that saves an opened file that needs (for the file name) the latest Sunday date

If you use the date string as part of a file name, you can't use any slashes.
Would need something like this:

Dim dte$
dte = Format(Date - 1, "yyyymmdd")

Or :

Dim dte$
dte = Format([I2], "yyyymmdd")

 

[Dobo_Bobo]

steve the fish and footoo, thank you very much for you help. I have learnt from both of you and this is the final code that works!

Sub PasteDateAsString()

Dim dte$

dte = Format([I2], "dd-mm-yy")

Range("H2").Value = dte

End Sub

This most likely could all be done within VBA, but whilst I learn it's easier for me to use cell references.

Many Thanks!