Results 1 to 5 of 5

How to save workbook without macro

This is a discussion on How to save workbook without macro within the Excel Questions forums, part of the Question Forums category; After populating my workbook using a macro I want to save workbook (with new name) but without macro.Copying the data ...

  1. #1
    New Member
    Join Date
    Dec 2002


    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.

  2. #2
    dk is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Sydney, Australia



    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.

    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
    Set VBComps = wbActiveBook.VBProject.VBComponents
    For Each VBComp In VBComps
       Select Case VBComp.Type
          Case vbext_ct_StdModule, vbext_ct_MSForm, _
             VBComps.Remove VBComp
          Case Else
             With VBComp.CodeModule
                .DeleteLines 1, .CountOfLines
             End With
       End Select
    Next VBComp
    Exit Sub
    MsgBox Err.Description, vbExclamation, "An Error Occurred"
    End Sub

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Sunny, spring-like Hull


    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-

    Public Sub CopyToNewWorkbook()
    Dim sh As Object

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


    End Sub

  4. #4
    New Member
    Join Date
    Nov 2010

    Default Re: How to save workbook without macro

    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?

  5. #5
    New Member
    Join Date
    Nov 2010

    Post Re: How to save workbook without macro

    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

    Cells.Select 'select all cells

    Workbooks.Add 'create new workbook


    ActiveWindow.FreezePanes = True

    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
    Workbooks("Processing.xls").Activate 'Current main file

    Sheets("Offer").Visible = False 'hide sheet

    Application.ScreenUpdating = True

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

    End If

    End Sub


Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts