HI Shane!
I think that you are half way to achive what you want in a fairly complete
manner. The reason why this will not work is because when you have the
Template open, and you save it as the new file, the fact is that you still
have the same file, just with a new name. So, you don't have the other file
any more. Two choices here, either you continue this road and then save the
file, close the file and open the template again, or what I am posting here.
As you will see, this is a little more structured, but with a huge
advantage, you don't have to be worry where the file is going to be save, or
pull as once is open, the program will always know where the file was
originally open, so it will know where to save it. Unless otherwise, this is
a good and stable manner to handle this kind of projects. Ok, please copy the
following and substitute your current macro. Let me know how does it work.
Suggestion: Copy the instruction (line) below, in the "This Workbook" part,
wherethe macros and modules are, but in the side of the sheets. This will
guarantee, that even if the program crash, anywhere, as soon you click the
mouse, it will pick up all the public variables. Here is the line
Location_Identification
Argy!
=================== Program ======================
Public Actual_Book As String
Public Actual_Path As String
Public Location As String
Public Location_1 As String
Public Name As String
Function Saving()
' Macro recorded 3/3/2002 by Argemiro Calderon
Location_Identification
Windows(Actual_Book).Activate
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=Location
ActiveWorkbook.SaveAs Filename:=Location_1
Application.DisplayAlerts = True
Windows(Actual_Book).Activate
End Function
Function Location_Identification()
Actual_Book = ActiveWorkbook.Name
Actual_Path = ActiveWorkbook.Path
Location = Actual_Path & "\" & Name
Location_1 = Actual_Path & "\" & Actual_Book
End Function
Sub PrintAndClear()
' PrintAndClear Macro
' Macro recorded 9/10/2008 by me
' Keyboard Shortcut: Ctrl+Shift+Q
Actual_Book = ActiveWorkbook.Name
response = MsgBox("Do you want to Save this to a new file?", vbYesNo,
"Save As")
If response = 6 Then
Name = InputBox("Enter a Filename", "Get Filename")
If Name = "" Then
MsgBox ("The name of the file is in blank. Please check and
hit ok to continue")
End
End If
Saving
' ActiveWorkbook.SaveAs Filename:="c:\" & Name & ".xls"
End If
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True '"I think
this line is not needed"
Workbooks.Open Filename:="P:\Calculations Sheet-2008 WSO.xls"
Range("E6").Select
End Sub
