showing date instead of formula

[kawnz]

I made a file called schedule.xls - it holds our department's time off requests. I put in 12 months, starting next month (1 Jul being start of fiscal year, etc.)

So, the first month is July, and the sheet is called JUL. The next sheet,AUG, calls up JUL's date and adds a month to it:

 =DATE(YEAR('JUL'!A1),MONTH('JUL'!A1)+1,1)

(I did this, so that if someone wants to change the starting date, it changes the date throughout the workbook).

It works fine, AUG shows August, etc.

The next thing I have it do, is related to another question I posted, about removing comments when sending the sheet to someone else. IE the file has comments telling the supervisor why John wants the day off , but wants to remove them so that Frank and Mary won't know.

So... I created a macro that singles out the one sheet the supervisor is working on (eg August). I copy it to a new workbook, then delete the comments, and then save the file with fileformat yyyymmschedule.xls

This is all working nicely...

However, and here is my question...

When it saves the new workbook, and I open it up, the cell 'formula' looks as follows:

 =DATE(YEAR('[schedule.xls]JUL'!A1),MONTH('[schedule.xls]JUL'!A1)+1,1)

I really would just like it to have the date value 08/01/2004 and display August 2004 without having any links to the original schedule file and that awful code.

How would I go about doing this?

 

[kawnz]

never mind... the answer was already in the code I had created, just waiting to come out

Sub create_new_sheet
    dt = Range("A1").Value
    ActiveSheet.Select
    ActiveSheet.Copy
    Cells.Select
    Selection.ClearComments
  [b]  Range("A1").Value = dt [/b]
    ActiveWorkbook.SaveAs Filename:=Format(dt, "yyyymm") & "schedule", FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
End Sub

I had the

dt = Range("A1").Value

already to generate the file name

I didn't think about using that same value to enter the date value into the new sheet. Adding

 Range("A1").Value = dt

worked.