Quoted:
Tag for some vba when I get to the office. View Quote
Here's some VBA code to do that for you. You'll need to use it in each workbook. Run the 'SplitWorkbookSheets' sub.
@OP, you know how to use Excel's built in VBA?
' Set variables
Public ThisBook, ThatBook, SavePath, SaveName, sLoop, SwapChar
Sub SplitWorkbookSheets()
' for the workbook this module is located, cycle through each tab (sheet), copy to a new file and save each new file with the tab name
' new files will save in the same directory as the source file.
' make sure none of the tabs has the same name as the source file
' set the source workbook name and path, activate the source book, if it's not already active
ThisBook = ThisWorkbook.Name
SavePath = ThisWorkbook.Path & "\"
ChDir SavePath
Windows(ThisBook).Activate
Application.ScreenUpdating = False ' turn off screen updating for speed
' loop through the sheets in the source book
For sLoop = 1 To Sheets.Count
Windows(ThisBook).Activate ' ensure the source book is active
Sheets(sLoop).Select ' select each sheet in order
SaveName = Sheets(sLoop).Name ' grab the sheet name
CleanTabName ' call sub to clean filename up
Sheets(sLoop).Copy ' copy the sheet to a fresh workbook. to delete the sheet from the source book, change .Copy to .Move
' This section saves the workbook in the same directory as the source file. The alerts are turned off so it won't error out if you try to overwrite an existing workbook
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=SavePath & SaveName & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ThatBook = ActiveWorkbook.Name
Windows(ThatBook).Activate
ActiveWorkbook.Close
Application.DisplayAlerts = True
Next sLoop
Application.ScreenUpdating = True ' turn the screen updating back on
End Sub
Sub CleanTabName()
' clean out characters that might choke windows filenaming conventions
SwapChar = "<"
SaveName = Replace(SaveName, SwapChar, "_")
SwapChar = ">"
SaveName = Replace(SaveName, SwapChar, "_")
SwapChar = ":"
SaveName = Replace(SaveName, SwapChar, "_")
SwapChar = "'"
SaveName = Replace(SaveName, SwapChar, "_")
SwapChar = "/"
SaveName = Replace(SaveName, SwapChar, "_")
SwapChar = "\"
SaveName = Replace(SaveName, SwapChar, "_")
SwapChar = "|"
SaveName = Replace(SaveName, SwapChar, "_")
SwapChar = "?"
SaveName = Replace(SaveName, SwapChar, "_")
SwapChar = "*"
SaveName = Replace(SaveName, SwapChar, "_")
SwapChar = "#"
SaveName = Replace(SaveName, SwapChar, "_")
SwapChar = "$"
SaveName = Replace(SaveName, SwapChar, "_")
SwapChar = "+"
SaveName = Replace(SaveName, SwapChar, "_")
SwapChar = "%"
SaveName = Replace(SaveName, SwapChar, "_")
SwapChar = "!"
SaveName = Replace(SaveName, SwapChar, "_")
SwapChar = "`"
SaveName = Replace(SaveName, SwapChar, "_")
SwapChar = "&"
SaveName = Replace(SaveName, SwapChar, "_")
SwapChar = "{"
SaveName = Replace(SaveName, SwapChar, "_")
SwapChar = "="
SaveName = Replace(SaveName, SwapChar, "_")
SwapChar = "}"
SaveName = Replace(SaveName, SwapChar, "_")
SwapChar = "@"
SaveName = Replace(SaveName, SwapChar, "_")
SwapChar = """"
SaveName = Replace(SaveName, SwapChar, "_")
End Sub
|
