Excel: create a file per sheet Solved

Question

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

Polux31 · Accepted Answer

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

loukoumeuh · Answer

Nickel, thank you very much.

bobleranger · Answer

Awesome, that's exactly what I was looking for!

Jpmaligne · Answer

I'm really bad at this, I have to do the same thing but I just don't know where to put the coding... Is it possible to help me?  Thank you

bigstyx · Answer

I am also a complete beginner, but I know how to do this ;)

1) Once the .xls file is open, press Alt-F11 to open the VB editor
2) Right-click anywhere in the project area in the top left, then Insert ==> Module
3) Copy/paste the code above into the right section, save it, and close the editor
4) To execute it, press Alt-F8 from the .xls, select the macro, and click on the Run button (I have the English version)
5) To assign a shortcut to this macro, instead of run, click on options

Good luck

Jpmaligne · Answer

Thank you,   I succeeded in everything; the only downside is that the sheets are saved in my documents, but that's okay, I just copy and paste them!

Polux31 · Answer

Hello Amandine,

If the original file is the one that contains the macro, simply change this line:

newWk.SaveAs (ws.Name & ".xls")

To:

newWk.SaveAs (ThisWorkbook.Path & "\" & ws.Name & ".xls")

;0)

--
"What one conceives well is clearly expressed, And the words to say it come easily."
Nicolas Boileau

glamedril · Answer

perfect, thank you very much, very useful!

Excel: create a file per sheet

8 answers

Texture issues in enshrouded

Gotek information

Self-invoking js function

My pc won’t charge anymore and runs super slowly

Download software against mcafee pop-ups

My computer stays connected for very little time

Unable to open xsl and xslx files

Archival hard drive no longer appears in file explorer

Samsung wallet

How to pair a receiver with an external speaker without fi