Save As without VBA project

[Gordon Rainsford]

After running some VBA code on an Excel file, I'd like to save the
file under a different name, but without saving the code. Is this
possible?

Alternatively I could copy the worksheet into a new file, but I need
to keep all the formatting including column widths & row heights.

Thanks,

Gordon Rainsford

 

[Steve Yandl]

Gordon,

Your idea to copy into a new file is a good option. The example below
assumes a workbook with three sheets. It will retain data and formatting
while leaving behind VBA in modules.

'-----------------------------------
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.xlsx"
objNewBook.Close

End Sub


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

Steve Yandl

 

[Peter T]

Sheets.Copy
Activeworkbook.saveas "c:\<path>\filename"

However if there is any code 'behind' the sheets, ie in chart/worksheet
modules, you'll need code to remove that code. See this page from Chip
Pearson

http://www.cpearson.com/excel/vbe.aspx

Regards,
Peter T

 

[Don Guillett]

If you save the file as an .xlsX file the code is removed. If desired then
save as .xls

 

[Gordon Rainsford]

It works great, Steve. Many thanks. But I'm puzzled as to why there's
no need to specify "Paste" or "Destination"?

Gordon

 

[JLGWhiz]

If you are using xl2003 or earlier, then it is just as easy to do a save as
and delete the code from the new workbook manually. If you are using xl2007
or later you can just save as .xlxs and it automatically omits the code.

 

[Don Guillett]

When you just copy a sheet it goes to a new wb. Could even be simpler.

Sub savenomacro()
Worksheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy
ActiveWorkbook.SaveAs Filename:="MyNewBook" 'default ext
ActiveWorkbook.Close ' closes new book if desired
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
It works great, Steve. Many thanks. But I'm puzzled as to why there's
no need to specify "Paste" or "Destination"?

Gordon

 

[Gordon Rainsford]

If you are using xl2003 or earlier, then it is just as easy to do a save as
and delete the code from the new workbook manually.  

Not really. The idea is for it to be used by people who don't
understand VBA, and to produce output that isn't going to trigger
warning messages when emailed or downloaded.


Gordon Rainsford

 

[JLGWhiz]

Oh, I didn't see that stipulation in the original post. Sorry.

If you are using xl2003 or earlier, then it is just as easy to do a save
as
and delete the code from the new workbook manually.

Not really. The idea is for it to be used by people who don't
understand VBA, and to produce output that isn't going to trigger
warning messages when emailed or downloaded.


Gordon Rainsford