Home
> service-is-programming > How to split an Excel workbook into one file per sheet with VBA (reworked)
How to split an Excel workbook into one file per sheet with VBA (reworked)
- There are other code snippets on stackoverflow.com, but I went with the top match in the Google search.
- Couldn’t get it to work (error # 424 Object required on xWS.copy in Excel 2013, with the VBA run from a separate utilities workbook) until I made these changes:
'TRP reworked http://www.extendoffice.com/documents/excel/628-excel-split-workbook.html
Sub Workbook_Split() 'Updated by TRP 20150924
Dim xPath As String
Dim myWorkSheet As Worksheet
xPath = Application.ActiveWorkbook.Path 'the source workbook needs be saved first
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWS In ActiveWorkbook.Sheets 'trp: replaced ThisWorkbook.Sheets
'trp: replaced xWS.Copy 'toask: this fails with 424: object required
Set myWorkSheet = xWS
myWorkSheet.Copy 'If you don't specify either Before or After, Microsoft Excel creates a new workbook that contains the copied sheet.
Application.ActiveWorkbook.SaveAs FileName:=xPath & "\" & myWorkSheet.Name & ".xlsx"
'trp: replaced xWS.Name & ".xls"
Application.ActiveWorkbook.Close False 'trp:savechanges:=False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Categories: service-is-programming
MS-Excel, VBA, worksheets
Leave a Reply
Questions? Read the About. Or just ask me a quick
FAQs for LRC student staff or for students or for teachers. To search our FAQs, in the browser addressbar, add after "https://plagwitz.wordpress.com/feed/?tag=faqs+/" "+TAG1" (from tag cloud below) OR "https://plagwitz.wordpress.com/feed/tag=faqs
&category_name=" "CAT1" (from category hierarchy below). OR search both categories and tags, and multiple TAGs/CATs (connect with "," for OR-search, with "+" for AND-search), like so: https://plagwitz.wordpress.com/feed/?tag=TAG1+TAG2+...TAGn&category_name=CAT1
+CAT2+...CATn"
Other ways to find help
If you cannot find it here, look there: 5,500 Language-Learning Links and Programs for learning or teaching 150 languages
Shortcuts:Our Lists, Our Maps, LRC Staff Moodle Site,LRC Project Moodle Site, 49erexpress, UNCC Moodle, Student Recordings: s:claslcslrcsanakostudent
Learning usage samples: Sanako oral exam, Kaltura webcam presentation, Dictation with speech recognition, Sanako written exam, Chinese and Japanese interactive stroke-order practice
Test the Sanako Installer, Webbrowser Popup Konfigurator for XP, or Windows7, faster LRC TeacherPC Log-in Let MS facilitate diacritics writing by installing for you US-International keyboard layout
This is my personal blog (Google+). The views expressed on these pages are mine alone and not those of my employer. The information in this weblog is provided “AS IS” with no warranties, and confers no rights.
Thomas' Work Space 
Thanks, it worked for me…