+ Reply to Thread
Results 1 to 4 of 4

Only save selected sheets in new file?

Hybrid View

jamewoong Only save selected sheets in... 11-05-2013, 04:27 PM
HaHoBe Re: Only save selected sheets... 11-06-2013, 12:23 AM
jamewoong Re: Only save selected sheets... 11-06-2013, 02:00 PM
HaHoBe Re: Only save selected sheets... 11-06-2013, 02:10 PM
  1. #1
    Registered User
    Join Date
    03-23-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    67

    Only save selected sheets in new file?

    Hi,

    Is there a way to only select some specific sheets in a workbook and save them in one single file (save as)?

    Client List.xls

    Summary:
    I create a macro that will do a save as with the current date... (Save Button in the Total Loss tab).

    What I want is when clicking on it, it will make a copy of the tab "Total Loss, Company 1, Company 2, Company 3" and will save *** a new file containing only those 4 tabs.

    Is that possible?

    Thanks

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 365 on Win11 (desktop), 2019 on Win11 (notebook)
    Posts
    8,198

    Re: Only save selected sheets in new file?

    Hi, jamewoong,

    maybe like this:
    Sub backup_1()
      Dim FName As String
      FName = ActiveWorkbook.Name
      Sheets(Array("Total Loss", "Company 1", "Company 2", "Company 3")).Copy
      ActiveWorkbook.SaveAs "C:\Files\" & Format(Date, "dd-mmm-yyyy ") & FName, FileFormat:=56
      ActiveWorkbook.Close False
    End Sub
    Please mind that you will get a message from VBA if you run the same routine more than once on a given day asthe workbook does already exist. You would need to give details if you replace it (use Application.DisplayAlerts = False before saving and turn the event on after doing so by Application.DisplayAlerts = True). If you need all of the copies please come back for more information on how to add a running numbe for that day. For my part I would prefer to have the date in the way of Year first, Month as Day following.

    Ciao,
    Holger
    Last edited by HaHoBe; 11-06-2013 at 12:32 AM. Reason: altered code to add FileFormat for the newly created workbook
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Registered User
    Join Date
    03-23-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    67

    Re: Only save selected sheets in new file?

    Thank you HaHaBe!

    I would like to know if there is a way to change the code to save it as the current Date + custom filename
    >>> 06-Nov-2013 Total Loss.xls

    Do you know how?

    Thanks again.

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 365 on Win11 (desktop), 2019 on Win11 (notebook)
    Posts
    8,198

    Re: Only save selected sheets in new file?

    Hi, jamewoong,

    current Date + custom filename
    What is custom filename? A constant? Where shall this be defined?

    ActiveWorkbook.SaveAs "C:\Files\" & Format(Date, "dd-mmm-yyyy ") & " Total Loss", FileFormat:=56
    Ciao,
    Holger

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA move multiple and selected sheets/tabs to new file and copy file as value
    By NeilM in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-01-2013, 06:19 AM
  2. [SOLVED] Save a selected subset of sheets
    By zenita in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-12-2012, 08:00 AM
  3. Save selected file as..
    By Pero in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-14-2009, 08:02 AM
  4. Auto save on exit selected data sheets, instead of whole project?
    By Bafa in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-09-2008, 01:38 PM
  5. [SOLVED] Creating a macro to open as save other selected Excel sheets
    By Wessie in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-28-2006, 04:40 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1