Saving a copied workbook - 2 questions

[jonco]

I have a workbook that has data for entire month. Date, Sales, Credits,
Debits, Expenses, etc.

At the end of the month when I make a copy of this workbook and paste it to
a new workbook the Date in the first row changed to the previous day (last
day of the previous month). All the other dates also change to match. I
end up with dates from Dec 31 to Jan 30th instead of Jan 1 - Jan 31st. The
other data remains correct and doesn't shift.

Here is the line of code that does the copy:

Workbooks("CheckUp.xls").Worksheets("CURRENT MONTH").Copy

Then I have vba open the SAVE AS dialog so the user can give it the name of
the previous month and save it.
Is there a way to automate naming the copied workbook with the name of the
previous month - ""Jan 07" (just month and year) for example?

fileSaveName = Application.GetSaveAsFilename( fileFilter:="Excel Files
(*.xls), *.xls")
If fileSaveName <> False Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=fileSaveName
Application.DisplayAlerts = True

Thanks for any help given. I cetrainly appreciate it.

Jon

 

[Guest]

Check out the 1904 Date System option under Tools options Calculation

It sounds like the option is checked in one workbook and not in the other one.
That will actually change the date by 4 years and 1 day. If you just have
the format to show MM/DD you would not notice the year being off.

I'll have to think about the other part of the question.

David

 

[merjet]

Workbooks("CheckUp.xls").SaveAs "Jan 07"
'to delete old-named file
Kill "C:\path\CheckUp.xls.xls"

Hth,
Merjet

 

[merjet]

Correction. Last line should have only one ".xls"

Hth,
Merjet

 

[jonco]

The month changes each month.

It won't always be Jan 07. Next month it'll be Feb 07. It needs to get the
date from a cell..... Cell A4 for instance.



Thanks'

Jon

 

[David]

Workbooks("CheckUp.xls").SaveAs Format(Range("A4"), "mmm yy") & ".xls"

 

[jonco]

Thanks for the answers guys. That looks like it will answer my questions.
I DO appreciate the assistance.