File SaveAs

[crmulle]

I perform a file save as in the workbook but when I do this I the original
file is no longer open. This causes me to manully reopen the original file.
I would like the original file to remain open even during or after the new
workbook is created.

Any ideas....thank you.

My current code is below:

ActiveWorkbook.SaveAs Filename:="WHLS Oversight Rpt " & Format(Date,
"mmddyy") & ".xls", FileFormat:=xlExcel8, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

Application.DisplayAlerts = True

Application.ScreenUpdating = True

ActiveWorkbook.Close

End Sub

 

[Steve Yandl]

Use the 'SaveCopyAs' method instead of 'SaveAs'.

If you're only interested in a single worksheet (or several sheets but not
the entire workbook) being made into the new workbook, you can use the
'Copy' method on worksheets but don't use 'Paste' and you end up with a new
workbook with just those sheets that becomes the active workbook.


Steve Yandl

 

[crmulle]

Steve,

I need to save the file with the new changes under a new name but also keep
the old file open...does that make sense?

 

[Steve Yandl]

That does make sense. Using SaveCopyAs will create a near exact duplicate
of your workbook and it will become the new active workbook. As I recall,
you don't retain any VBA code stored in modules but you will get all your
data, formatting and code attached to the workbook or individual worksheets.
If you close that workbook (as opposed to closing Excel), your previous
workbook should once again be the active workbook. I need to run a test but
I think that you should be able to simply add a line of code to close the
active workbook and the copy will be closed with the new name you gave it
and your starting workbook should be active and ready to go again.

Steve

 

[Steve Yandl]

Here is a copy of something that should work for you. You can amend the
line with the array if you don't want to carry over Sheets one through
three.

'-----------------------------------
Sub SaveWithoutMacro()

Dim intOpens As Integer

intOpens = Application.Workbooks.Count

Worksheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy

Set objNewBook = Application.Workbooks(intOpens + 1)
objNewBook.Activate
objNewBook.SaveAs Filename:="MyNewBook.xls", FileFormat:=xlExcel8
objNewBook.Close

End Sub


'-----------------------------------

Steve Yandl