Excel

Save Copy of Workbook Minus Some Sheets

Ease of Use

Intermediate

Version tested with

2000 

Submitted by:

johnske

Description:

A copy of the workbook will be saved to your default location less any sheets you want to keep from being copied. 

Discussion:

You may have a 'general-purpose' workbook that you need a copy of for a customer, for staff, or to email. However, there may be information on some worksheets that is not intended for the eyes of any others. You can specify what sheets are NOT to be copied and whenever you need to create copies you can rest assured that however hurried you may be, those sheets will not fall into the hands of others. 

Code:

instructions for use 

			
Option Explicit 
Option Compare Text 
 
Sub SaveCopyMinusSome() 
    Dim ThisBook As Workbook, WkSht As Worksheet 
    Set ThisBook = ThisWorkbook 
    Application.ScreenUpdating = False 
    ThisBook.SaveCopyAs Left(ThisBook.Name, _ 
    Len(ThisBook.Name) - 4) & " Copy.xls" 
    Application.Workbooks.Open (Application.DefaultFilePath & "\" & _ 
    Left(ThisBook.Name, Len(ThisBook.Name) - 4) & _ 
    " Copy.xls") 
    For Each WkSht In ActiveWorkbook.Worksheets 
        Select Case WkSht.Name 
             
             '***************************************
             'Sheets to be excluded (rename as required)
        Case "Private", "Confidential", "Personal", "Secret" 
             '***************************************
             
            Application.DisplayAlerts = False 
            WkSht.Delete 
        Case Else 
             'do nothing
        End Select 
        Application.CutCopyMode = False 
    Next WkSht 
    ActiveWorkbook.Save 
    ActiveWorkbook.Close 
    MsgBox "Copy Saved as: " & Left(ThisBook.Name, _ 
    Len(ThisBook.Name) - 4) & " Copy.xls" & vbLf & _ 
    "This copies location is: " & Application.DefaultFilePath 
End Sub

How to use:

  1. Open an Excel workbook
  2. Select Tools/Macro/Visual Basic Editor
  3. In the VBE window, select Insert/Module
  4. Copy and paste the code into the Module
  5. Now select File/Close and return to the main application
  6. Dont forget to save your changes...
 

Test the code:

  1. Change the name(s) of any sheets that you need to where it's indicated in the code
  2. (NOTE: You can add further names to the list if you like, just include them in quotation marks and separate them with commas next to the others)
  3. In the main window, select Tools/Macro/Macros.../SaveCopyMinusSome/Run
 

Sample File:

SaveCopyAsMinusSomeSheets.zip 9.7KB 

Approved by mdmackillop


This entry has been viewed 287 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express