Excel: create a file per sheet
Solved
loukoumeuh
-
Thelastboyscout -
Thelastboyscout -
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.
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 réponses
Hello,
Copy/Paste this into a module using the VB editor:
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
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
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...
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