How to save workbook without macro

Georgeh

New Member
Joined
Dec 11, 2002
Messages
1
After populating my workbook using a macro I want to save workbook (with new name) but without macro.Copying the data to another workbook loose my sheet formating.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Hello,

You can do this with a macro. Copy this code into a standard module.

Please note the bit about setting the reference to the extensibility library.

Rich (BB code):
Sub SaveWithoutMacros()

'Purpose : To save a copy of the active workbook without macros

'PLEASE NOTE : You must choose Tools, References and select the
'Microsoft Visual Basic for Applications Extensibility library

Dim vFilename As Variant
Dim wbActiveBook As Workbook
Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents


On Error GoTo CodeError


'Get a filename to save as
vFilename = Application.GetSaveAsFilename(filefilter:="Microsoft Excel Workbooks,*.xls", _
                                        Title:="Save Copy Without Macros")
                                        
If vFilename = False Then Exit Sub  'User chose Cancel

ActiveWorkbook.SaveCopyAs vFilename
Set wbActiveBook = Workbooks.Open(vFilename)


'Now strip all VBA, modules, userforms from the copy
'This code is from Chip Pearson's website http://www.cpearson.com

Set VBComps = wbActiveBook.VBProject.VBComponents

For Each VBComp In VBComps
   Select Case VBComp.Type
      Case vbext_ct_StdModule, vbext_ct_MSForm, _
            vbext_ct_ClassModule
         VBComps.Remove VBComp
      Case Else
         With VBComp.CodeModule
            .DeleteLines 1, .CountOfLines
         End With
   End Select
Next VBComp

wbActiveBook.Save

Exit Sub

CodeError:
MsgBox Err.Description, vbExclamation, "An Error Occurred"

End Sub
 
Upvote 0
If you have no code in worksheet modules, (or even if you have and want to keep it), the following will copy all your sheets into a new workbook without copying standard modules and should preserve the formatting-
<pre>
Public Sub CopyToNewWorkbook()
Dim sh As Object

ThisWorkbook.Activate
For Each sh In ThisWorkbook.Sheets
sh.Select False
Next sh

ThisWorkbook.Windows(1).SelectedSheets.Copy

End Sub
</pre>
 
Upvote 0
I realize that this is an old thread, but I came across it while looking to perform this action (save without macros). I copied the code above, added a browse to file and asked the user "Are you sure?" before performing the save, then closed the file at the end. I have not altered the body of the code at all, but I am having the following problem:

I need to run the code twice on a file to get all the macros stripped out. The first time clears out all code in the Sheets (where I have buttons to call subroutines in the Modules), but leaves the UserForms and Modules intact. The second time clears these out. Any ideas what the problem is here?
 
Upvote 0
I ain't an expert with VBA but more of a record macro, google it and copy and paste guy.

Here is code that I use to archive without the macro.. it's basic copy and paste for a single sheet but if effective for my purposes. Hope this helps someone else.

--------------------------------------------------------------------------

Sub Archive()

'Use MsgBox to ask user for confirmation
Dim iSave As Integer

iSave = MsgBox(prompt:="Archive this report?", _
Buttons:=vbYesNo, Title:=" Archive Report")

Application.ScreenUpdating = False

If iSave = vbYes Then

'Code to save data in the offer sheet to a new workbook and save to predetermined location
Sheets("Offer").Visible = True 'show sheet
Sheets("Offer").Select

Cells.Select 'select all cells
Selection.Copy

Workbooks.Add 'create new workbook

ActiveSheet.Paste

'formatting
Rows("1:1").EntireRow.AutoFit
Range("B2").Select
ActiveWindow.FreezePanes = True
Range("A1").Select

Reportdate = Format(Now, "YYYYMMDD HHMM") 'Reportdate is a date and time of now

'filesavename predetermined
filesavename = ("Report created by ") & (Application.UserName) & (" on ") & (Reportdate) & (".xls")

' MsgBox "Save as " & FileSaveName
'If vbOK Then

'savelocation predetermined
ActiveWorkbook.SaveAs Filename:="\\SERVER\Archive\" & (" ") & (Reportdate)

'hide offer sheet and bring back userform1
UserForm1.hide
Workbooks("Processing.xls").Activate 'Current main file
Range("D5").Select

Sheets("Offer").Visible = False 'hide sheet
Sheets("Info").Select
UserForm1.show

Application.ScreenUpdating = True

ElseIf iSave = vbNo Then 'if no, stop
Exit Sub

End If

End Sub

--------------------------------------------------------------------------
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top