Macro Help

[Sigmund]

I have created an Excel Template and a macro that save it under a new name
the problem is that everytime it saves under new name the new book stays
open as well as the template; i need the template to stay open and the book
created close after saving; here is what i have so far
Sub PrintAndClear()
'
' PrintAndClear Macro
' Macro recorded 9/10/2008 by me
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
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 End
ActiveWorkbook.SaveAs Filename:="P:\" & Name & ".xls"
End If
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Workbooks.Open Filename:="P:\Calculations Sheet-2008 WSO.xls"
Range("E6").Select
End Sub



i tried adding
ActiveWorkbook.Close after the print out but it does not work it closes the
book and the template but leaves Excel running

your help will be greatly appreciated

 

[Dave Peterson]

Are you sure that you end up with the template still open and the newly saved
workbook still open?

I would have guessed that you'd see the newly saved workbook, but the template
would have "disappeared".

But if you want to save (and close) a workbook and keep the template a template,
try using .SaveCopyAs instead of .saveAs.

ps. I'm not sure what that workbooks.open line is doing. Could that be causing
part of the confusion?

 

[Sigmund]

Changing to SaveCopyAs worked fine but know it gives me message
Calculation Sheet 2008-WSO 9Name of the Template) is already Open, reopening
will cause any changes you made to be discarded. do you want to reopen (Name
of the Template) Yes - No
is there a way that the macro can answer yes to that without user
intervention?
if so what is the VBA command?
Thanks again

 

[Argy - Arcasoft]

Hi Sigmund:

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

 

[Dave Peterson]

Why reopen the workbook?

Just remove that line from your code.

And just to make sure...

You're using an existing .xls as a template file. You're not using a .xlt (a
real template file), are you?

=======
If you want to reinitialize any of the cells in the "template" file after you've
done the .savecopyas, just add a routine that empties/resets to 0 (or whatever
you need to do) all the input cells.