ThunderGod
New Member
- Joined
- May 31, 2012
- Messages
- 7
I have created 2 Template files (xltm). For my purposes here one is named Master.xltm the other is named EmailFile.xltm. EmailFile.xltm is referencing Master.xltm ie: =[Master.xltm]sheet1!C$6$ . Both of these files are opened at once via Autpen Macro in Master.xltm. Once the data is entered into Master.xltm it updates EmailFile.xltm. I have a macro in Master.xltm that saves the file as a cell value, which is okay for the Master.xltm workbook. What I am trying to do is save not only the Master.xltm file but also the EmailFile.xltm as well as xlsx files and remove all Macros and Buttons from the saved files and remove all formulas but retain the Values. The resulting file from EmailFile.xltm will be emailed.
I am wanting the files names to be similar to the example below.
File_For_09-07-2012.xlsx (derived from Master.xltm)
Email_File_09-07-2012.xlsx (derived from EmailFile.xltm)
I want to be able to do this by using the Button I created in Master.xltm
The 2 macros I have in Master.xltm are coded below.
I have created a Button in Master.xltm to run the File Save.
FileSave Macro
Autpen Macro created with Macro recorder
I hope I clarified what I am trying to do.
I am wanting the files names to be similar to the example below.
File_For_09-07-2012.xlsx (derived from Master.xltm)
Email_File_09-07-2012.xlsx (derived from EmailFile.xltm)
I want to be able to do this by using the Button I created in Master.xltm
The 2 macros I have in Master.xltm are coded below.
I have created a Button in Master.xltm to run the File Save.
FileSave Macro
Code:
Sub FileSave()
Dim Variable1 As String
Dim Variable2 As String
Variable1 = Sheets("Sheet1").Range("C6").Value + 12
Variable2 = Format(Variable1, "mm-dd-yyyy")
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\Excel Testing Folder\" & "File_For_" & Variable2, _
Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True
End Sub
Autpen Macro created with Macro recorder
Code:
Sub Auto_Open()'
' Auto_Open Macro
'
'
ChDir "C:\Excel Testing Folder"
Workbooks.Open Filename:= _
"C:\Excel Testing Folder\EmailFile.xltm", Editable:=True
End Sub
I hope I clarified what I am trying to do.