Weird behaviour in workbooks that reference file names.

[Kayslover]

Hi,

Sorry this is a bit convoluted and I am therefore giving a background and what the problem is.

Background and information.

I have a template and I execute a macro to get a year from the user by using an InputBox command.

This year is stored in Cell R2 in sheet named Formula.

The template is then saved.

In the template, in sheet named Formula, Cell S2 there is a formula to get the file name of the workbook using:-

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

Also in the template, in sheet named Formula, Cell T2 there is a formula to get the first 2 characters of the file name of the workbook using :-

=LEFT(S2,2)

Also in the template, in sheet named Formula, Cell Q3 there is a formula to get the first Sunday of the month using:-

=EOMONTH(Q2,-1)+1+MOD((7-WEEKDAY(EOMONTH(Q2,-1)+1,11)),7)

by looking at Cell Q2. Q2 contains the following formula:-

=CONCATENATE(1,"/",T2,"/",R2)

T2 from above and R2 from above.

In the template, Sheet named Sheet1, Cell B4 is a formula that refers to =Formula!Q3. B5 to B10 in the sheet has the formula =B4+1, =B5+1, etc. etc. etc. to get the next day of the week.

In the template, Sheet named Sheet2, Cell B4 has the formula =Sheet1!B10+1, B5 to B10 has the formula =B4+1, =B5+1 etc. etc. etc. to get the next day of the week.

In the template, the above also happens to sheets named Sheet3, Sheet4 and Sheet5.

In the template, sheet named Monthly Totals has the formula:-

=Formula!E2

E2, itself has the formula

=A1-DAY(A1)+1

, which gets the first day of the month.

Once the template has been saved with R2 populated by the year that the user has typed, I execute a macro to create 12 workbooks using the commands:-

ActiveWorkbook.SaveAs Filename:=fPath & "01 January.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

ActiveWorkbook.SaveAs Filename:=fPath & "02 February.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

ActiveWorkbook.SaveAs Filename:=fPath & "03 March.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

ActiveWorkbook.SaveAs Filename:=fPath & "04 April.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

There is a command at the beginning of the macro which reads:-

Dim ws As Worksheet

fPath = ThisWorkbook.Path & "\"

Now the actual problem.

When I open 01 January, the values in Sheet named Sheet1 B4-B10 are as I expect, starting with the first Sunday of January in B4, B5-B10 are next days. Sheets named Sheet2, Sheet3, Sheet4 and Sheet5 have the dates as expected in Cells B4-B10.

When I now open 02 February, all is well as 01 January.

However, when I switch to 01 January, Sheet named Sheet1 has the same dates as those in 02 February. I also notice in sheet named Formula, cells Q2 is now populated with values from 02 February.

If I now open up 03 March, and switch to 01 January and 02 February, Sheet named Sheet1 has the same dates as those in 03 March.

The only way, I can get the same consistent dates is once I open the individual workbooks is to go to the sheet named Formula, select cells that have the formulas for File Name (S2), First Sunday of the (Q3) month, first 2 character of the file name (T2), then copy them and then paste values into themselves.

Is there a way to stop this weird behaviour? Other than replacing the filenames with their physical values (using copy, then paste special values).

Hope someone can help and hope people understand what I am saying.

 

[ExcelGzh]

After you create each file, you need to do a recalculate and then save the file again otherwise the result of the formula that generates the filename stays as it was before the save (the name of the original file).

 

[Kayslover]

ExcelGzh,

Thanks for the info, will ensure I do what you are recommending.