Save As without VBA project

G

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
 
S

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
 
D

Don Guillett

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

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
 
J

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.
 
D

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
 
G

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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top