Save a copy of Workbook on Close to a different folder.

[bubai]

Dear Guys,
I've got a Workbook "F:\MyFolder\Cost.xlsm" where I'd like to be a code which would enable me to save a copy of this workbook to a different folder On Close and the copy should be A MACRO FREE WORKBOOK (e.g. "F:\CopyCost.xlsx"). I've created the following code, but it produces a workbook which cannot be opened. Help please---

Please Login or Register  to view this content.

[AlphaFrog]

Try this. It assumes there is no vba code in the worksheet code modules.

Please Login or Register  to view this content.

[bubai]

AlphaFrog,
Firstly, that code creates an empty workbook. Secondly, it saves the workbook to the same directory, whereas that should be saved in a different one "f:\CopyCost.xlsx".

[AlphaFrog]

It worked for me in my test. Copied the workbook as a macro free workbook to a different folder. The sheets were not blank.

[bubai]

AlphaFrog
I have made little alteration to your code. Now it seems to work fine.

Please Login or Register  to view this content.

Thanks.
Just one more question - What was wrong with "ThisWorkbook.SaveCopyAs "F:\CopyCost.xlsx" function?"
Should I mark this as Solved?

[AlphaFrog]

You're welcome.

- Just re-naming an existing workbook with the .xlsx extension doesn't strip it of macro code and it doesn't change it to the .xlsx format.
- The .SaveCopyAs function cannot save a copy as different file format. It wouldn't be a copy then.

Should you mark this as solved? Only you can answer that.

[bubai]

AlphaFrog,
Marking it unsolved as i've got 2 questions.
1. Why there is no Paste Method but only Copy Method "Sheets.Copy".
2. What method should i add to the code if i'd like to save the copy of the workbook without any formula. Just with values.

[AlphaFrog]

1.) Highlight the word Copy in your VBA code editor and press F1 for help on that topic. Under the applies to Sheets section in the Remarks area gives an explanation of its usage for this instance.

2.)

Please Login or Register  to view this content.

[bubai]

Thaks a lot AlphaFrog.