El año pasado tenía la misma necesidad, de tomar un libro de excel y obtener un archivo por cada hoja.
Luego de investigar en varios foros y obtener códigos, pude depurar el código para hacerlo lo más sencillo posible (a mi modo de ver)
espero que le sirva.
Código:
Sub GenerarArchivos()
' Esta macro abre un cuadro de dialogo para buscar un archivo de excel
' una vez seleccionado, convierte cada hoja en un archivo separado
' Macro creada 28/04/2009 por Abiel.Deleon
MiFile = Application.GetOpenFilename("Archivos de Excel,*.xl*") 'pide abrir un archivo xls
If MiFile = False Then Exit Sub 'si CANCEL entonces no hace nada
Workbooks.Open Filename:=MiFile, ReadOnly:=True 'abre el archivo como solo lectura
Application.ScreenUpdating = False 'oculta el proceso
With ActiveWorkbook
For Each Hoja In .Sheets 'para cada hoja va a crear una copia y guardar
Hoja.Copy
NewFile = .Path & "\" & Hoja.Name & ".xls" 'guarda usando el prefijo
'se usa la forma <ruta> \ <nombre> . <extension>
'si existe el archivo, lo sobreescribe
If Dir(NewFile, vbArchive) <> "" Then Kill NewFile
ActiveWorkbook.SaveAs Filename:=NewFile 'comando para guardar la hoja
ActiveWorkbook.Close SaveChanges:=False 'despues de crear la hoja, la cierra
Next Hoja
.Close SaveChanges:=False 'cierra el archivo abierto
End With
Application.ScreenUpdating = True
End Sub
ATENCION: los archivos de excel pueden dar error al abrirse dependiendo de la versión desde la que se generan. Por ejemplo si desde Excel 2007 usted guarda el archivo como .xls, tenga cuidado con este detalle.