Save As without VBA project
 

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

Save As without VBA project
 

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


"Gordon Rainsford" wrote in message
...
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

Save As without VBA project
 

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


"Gordon Rainsford" wrote in message
...
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

Save As without VBA project
 

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gordon Rainsford" wrote in message
...
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

Save As without VBA project
 

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

Gordon


On Nov 26, 6:13*pm, "Steve Yandl" wrote:
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

"Gordon Rainsford" wrote in message

...



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- Hide quoted text -

- Show quoted text -

Save As without VBA project
 

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.


"Gordon Rainsford" wrote in message
...
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

Save As without VBA project
 

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

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

Gordon


On Nov 26, 6:13 pm, "Steve Yandl" wrote:
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

"Gordon Rainsford" wrote in message

...



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- Hide quoted text -

- Show quoted text -

Save As without VBA project
 

On Nov 26, 8:27*pm, "JLGWhiz" wrote:
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

Save As without VBA project
 

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


"Gordon Rainsford" wrote in message
...
On Nov 26, 8:27 pm, "JLGWhiz" wrote:
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