SaveAs Multiple Worksheets into Multiple Workbooks

  • I have a workbook of 50+ worksheets. The worksheet names are 100, 101, 102 ... 199, 200, 201... 299 etc...and "Data". I need to group and SaveAs all "100", "200", etc worksheets along with the Data tab (to calculate all the formulas) as individual workbooks.

    I have been searching and tried many methods, but nothing has work. Could someone please advise this is possible?

  • Re: SaveAs Multiple Worksheets into Multiple Workbooks

    I am not sure where (or when) exactly you want to invoke the code to copy these "groups" of worksheets into new workbooks but the following code should at least point you in the right direction of how to achieve this:

    One option would be to place this code in the Workbook_BeforeClose or Workbook_BeforeSave routines.

    Also, if you wanted to move rather than copy the sheets, you would need to change the wSheet.Copy to a wSheet.Move (I am assuming you don't want to move the "Data" sheet, just copy it).

  • Re: SaveAs Multiple Worksheets into Multiple Workbooks

    Hi gijsmo, thank you for writing this code. If possible, I want to save these new workbooks with the name "100", "200" etc..."commission month"

    Also, as I am testing the marco, I notice there are a few more sheets I need to copy over as well in order for the calculation to work. Here is a list of tabs I need to bring over..
    "AE Flu Tracings"

    These tabs need to be hidden too.


  • Re: SaveAs Multiple Worksheets into Multiple Workbooks


    I have updated the code to accommodate what you asked, the only thing I didn't understand was the "commission month". However, if it relates to changing the final workbook name from "100", "200", etc to "100-Jun-11", "200-Jun-11", then it won't be hard to see where this needs to be done:

    The code is a bit more complex now as there are additional subs required to perform the task. The main sub CopySheetsToNewWorkbooks is still the one you need to call to perform the work.

    Hopefully, the comments will make the rest self-explanatory! And hopefully, you can see how this can be relatively easily expanded to include "300", "400", etc.

  • Re: SaveAs Multiple Worksheets into Multiple Workbooks


    I think we are almost there!!! My only issue is that because every sheet is copy individually, the formula is referencing the master workbook and not the new "data" tabs copied. Is there a way to copy the group of worksheets together? Or a code to reference to the new workbooks?


  • Re: SaveAs Multiple Worksheets into Multiple Workbooks

    I don't have a quick VBA solution right now but one way to fix this I think is to edit the links. In Excel 2003, select Edit --> Links on the newly created spreadsheets. Then select the Change Source option and change the source of the links from the Master spreadsheet to the newly created spreadsheet (essentially point the links back to itself). If this is successful, the Edit --> Links option will no longer be available in the new spreadsheet as there are no longer any external links.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!