+ Reply to Thread
Results 1 to 4 of 4

Save excel sheets as separate files except sheet1

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    12-19-2012
    Location
    Bengaluru
    MS-Off Ver
    Excel 2013
    Posts
    108

    Save excel sheets as separate files except sheet1

    Hi

    I need a help regarding the below problem i'm facing

    I have an excel workbook with multiple sheets and each sheet Except Sheet1 and Sheet 2 needs to be saved as a separate excel workbook with the sheet name as the file name. The master sheet should remain intact without any changes.

    I'm using the below code but it is not working. Can some one please help on this

    Sub SaveAll()
      Dim wbk As Workbook
      Dim wsh As Worksheet
      Set wbk = ActiveWorkbook
      For Each wsh In wbk.Worksheets
            If Wsh.Name <> "Sheet1" Then wsh.Copy
            ActiveWorkbook.SaveAs Filename:=wsh.Name & ".xls"
      Next wsh
    End Sub
    Thanks in Advance.

    Regards
    Kittu
    Click on the star * if some1 helped you.

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,641

    Re: Save excel sheets as separate files except sheet1

    Hi there,

    The following code seems to work for me:

    
    
    Sub SaveAll()
    
        Dim wbk As Workbook
        Dim wsh As Worksheet
    
        Set wbk = ActiveWorkbook
    
        For Each wsh In wbk.Worksheets
    
            If wsh.Name <> "Sheet1" And _
               wsh.Name <> "Sheet2" Then
    
                wsh.Copy
                ActiveWorkbook.SaveAs Filename:=wsh.Name & ".xls"
                ActiveWorkbook.Close SaveChanges:=False
    
            End If
    
        Next wsh
    
    End Sub

    Hope this helps - please let me know how you get on.

    Regards,

    Greg M

  3. #3
    Forum Contributor
    Join Date
    12-19-2012
    Location
    Bengaluru
    MS-Off Ver
    Excel 2013
    Posts
    108

    Re: Save excel sheets as separate files except sheet1

    Thanks Greg.

    That Helps. Rep Added

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,641

    Re: Save excel sheets as separate files except sheet1

    Hi again,

    Many thanks for your feedback and also for the Reputation increase - much appreciated

    You're welcome - glad I was able to help.

    Best regards,

    Greg M

+ 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. [SOLVED] save all sheets as separate text files
    By Minnesaenger in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-06-2015, 02:25 AM
  2. Merge multiple excel files into one file and separate datas into 2 sheets
    By 2013boris in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-14-2014, 05:57 AM
  3. Macro Multiple excel files into one workbook as separate sheets
    By ian_a91 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-24-2014, 08:54 AM
  4. Split the excel data in worksheet to save it in two separate excel files
    By sat1591 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-20-2014, 04:52 AM
  5. Replies: 1
    Last Post: 08-20-2013, 11:13 AM
  6. [SOLVED] How to link two sheets in two separate Excel files
    By BNCOXUK in forum Excel General
    Replies: 3
    Last Post: 11-30-2012, 09:17 AM
  7. How do I save sheets in a workbook to separate files?
    By Omzala in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-13-2005, 03:06 PM

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