Excel: create a file per sheet

Solved
Hello,

First of all, this forum is a real goldmine of information, thanks to everyone.

I have an Excel 2003 file that contains many sheets (several dozen), and I would like to separate all the sheets and get one file for each sheet. Manually, it's doable (right-click on the sheet, copy to new workbook, etc ...) but it's cumbersome when there are many sheets. The icing on the cake would be that each new file is named after the original sheet.

Am I being clear?
If you could give me some pointers in the Excel menus or for making a macro, I would be delighted.

Thank you in advance.
Configuration: Windows XP Firefox 3.5.2

8 answers

  • Votes
  • Date
  • Descending date
  1. Hello,

    Copy/Paste this into a module using the VB editor:

    Sub saveOnglet() Dim ws Dim newWk As Workbook For Each ws In Worksheets Set newWk = Workbooks.Add(xlWBATWorksheet) ws.Copy newWk.Sheets(1) newWk.SaveAs (ws.Name & ".xls") newWk.Close Set newWk = Nothing Next ws End Sub


    Then assign a shortcut to the macro.

    ;o)
    --
    “What is well conceived is clearly stated, And the words to say it come easily.”
    Nicolas Boileau
    1. Sorry for the double post, I can't find the "edit" button...

      The only issue is that, since my file is large, Excel crashes and doesn't save the files on the grounds that it doesn't have enough resources available... boo or hoohoohoo...
    2. @loukoumeuhSorry for the double post, I can't find the "edit" button ...

      Which button are you talking about?

      Unfortunately, I can't do anything about your issue ...

      ;o)
      --
      “What is well conceived is clearly expressed, And the words to say it readily come.”
      Nicolas Boileau
    3. @Polux31Hello,

      Sorry for not responding sooner.
      Actually, when you post a message, you can edit it for 60 minutes (I was looking for that button), but since I'm not registered, it's not possible.

      As for my issue, Excel complains, but it still does the job, so we can consider the problem solved!

      Thank you very much!!! I'm delighted.
    4. That's great! Thank you, it's working really well!!!!
    5. Thank you very much, awesome.