Creating and saving new workbooks from one source

[oddinho2]

Hi I am pretty new to VBA, I have a spreadsheet that I am using called "FX Master Worksheet.xlsm" - we can just refer to it as the source file going forward. In it there are two worksheets (one called "TDB" and one called "MTS").

Every day I use the source file and then have to save it as a copy and rename it "Today's FX sheet.xlsx" - we can call it the new file going forward.


Then I copy the "MTS" worksheet from the new file and save it as a copy as a csv file named "Today's MTS file.csv".


I am trying to insert VBA code into my existing macro but all I keep doing is renaming the source file instead of the new xlsx or csv files. Can anyone help?

I will insert my current code and maybe you can tell me what and where I should insert. Stumped! I just want to end up with the source file untouched, and the new xlsx and csv files renamed as above and saved in the location "G:\FX\"

PS if I didn't notate the code correctly let me know. I don't know code but copied what i could from other sheets I found in similar files and tried to adapt to suit my requirements.

Please Login or Register  to view this content.

[Richard Buttrey]

Hi

You should save your Master workbook as an Excel Template file .xltm. Save it in your default folder for Templates. Then every day you can open the template and when you choose to save it, it will automatically save as a .xlsm file and it won't overwrite your original template.

Your saving macro seems a little complicated. Why not just use

Sheets("MTS").Copy

This will create a new workbook with a single sheet called MTS.

[oddinho2]

Thanks Richard, the reason for the additional sheet being created is that I am the user of the sheet primarily, but when others use the sheet they accidentally save over the original, or save over a previously saved file (I overly simplified the instructions above to remove all of the code that isn't pertinent to this discussion - there are other sheets in the original and protection etc that i remove and reformat after creating the new sheets).

Do you know of any code that could help?