Excel Avanzado

Macros, Vba en Excel y muchos ejemplos de nuestro Curso de Excel Avanzado

Excel Avanzado

Crear un Archivo por cada hoja de Excel

| 13 comentarios

Crear un archivo por cada hoja Excel es usado especialmente para organizar datos. Cuando estos datos se encuentran en el mismo archivo pero necesitan ser distribuidos para distintas áreas o personas a las cuales solo se les debe dar una porción de la información que se tiene en el archivo.

Para esto se necesita crear un archivo Excel por cada hoja que se tiene,  y en aquel archivo debe encontrarse toda la información que se encuentra en esa hoja.  Para esto, se hace uso de WORKBOOKS.ADD. Sin embargo, para crear un archivo por cada hoja se debe de hacer  la misma acción el mismo número de veces que debe ser igual al mismo número de páginas. Por este motivo, primero de debe de crear la acción repetitiva para lo cual se puede usar FOR NEXT  o también se puede usar DO LOOP. En este caso usamos DO LOOP con la restricción de que la acción se realice hasta que una variable, a la cual llamaremos i, sea mayor al número de hojas que se encuentra en el archivo Excel. Para este ejemplo, las hojas que se encuentran en el archivo son las siguientes:

Tarea 5.1

Luego, seleccionamos la primera hoja y copiamos su contenido. Inmediatamente después agregamos un nuevo libro con WORKBOOKS.ADD y llamamos a otra variable con el mismo nombre de la hoja seleccionada. En la imagen se puede observar la creación del archivo.

Tarea 5.2

(Para mayor visualización de la imagen, hacer click en la misma)

Ahora, en este nuevo libro copiamos la información.

Tarea 5.3

(Para mayor visualización de la imagen, hacer click en la misma)

Después, guardamos el archivo con el nombre de la hoja en el mismo lugar que se encuentra el archivo original y cerramos el libro.

Finalmente hacemos que esta acción se repita, haciendo que la constante i aumente en uno hasta que llegue al número de hojas que se tiene en el Excel  y cerramos la acción con LOOP. Es así como tenemos varios archivos en el mismo lugar en donde se encuentre el archivo Excel original, como se muestra en la figura.

Tarea 5.4

Este ejemplo se encuentra en el siguiente archivo

Descargar ejemplo

Elaborado por: Fiorella del Carmen

Formato para crear archivo por cada Hoja

Luego de descargar el ejemplo (líneas arriba) para crear los archivos y en cuanto los haya probado notará que los archivos generados son archivos *.xls, esto se debe a una de las últimas líneas en las que aparece:

FileFormat:=xlNormal

Si desea cambiar el tipo de archivo generado puede reemplazar el código indicado, por las siguientes alternativas:

FileFormat:=56

FileFormat:=51

FileFormat:=52

FileFormat:=50

El 56 servirá para generar un *.xls (formato compatible con Excel 2003), el 51 generará un XLSX (archivo Excel normal -  sin macros),  el 52 nos dará un *.xlsm (libro de Excel habilitado para macros), y el 50 nos generará un *.xlsb (libro binario de Excel).

13 comentarios

  1. Tengo la siguiente instruccion para nombrar una hoja en funcion del valor de la celda "O8"

    Sub NombreHoja()
    ActiveSheet.Name = Range("O8").Value
    End Sub

    Pero necesito agregarle a esta macro que me nombre las 10 hojas que contiene el libro. Hoy lo que hago es ejecutar la macro hoja por hoja.

    Queria saber que debe agregarse a la macro para que cada hoja, de las 10 que posee el archivo quede nombrada con la celda "O8" de cada hoja, con una sola ejecucion.

    Muchas gracias

    • Después de
      ActiveSheet.Paste

      Añada:
      Range("O8").Select

      Saludos

  2. Excelente articulo, ya que trabajo con varios usuarios a los que les tengo que generar herramientas en excel que faciliten su trabajo.
    Gracias. Saludos !!!!!!!!!

  3. hola muy buen articulo!!, me gustaría hacer lo mismo pero con hojas ocultas, es posible?

  4. Hola tengo problemas con la macro me crea archivos de 65536 como hago para ampliarlo

    • Gracias por el mensaje Luis

      El lo indicado se produce por el formato empleado , pruebe usar FileFormat:=51 o FileFormat:=52 según sea necesario.

      Saludos

  5. Hola estimado tengo una consulta, trato de reutilizar su macro en otro archivo pero por alguna razón se activa el libro con el ejemplo que ud. tiene en la descarga, como lo puedo hacer para que siga ejecutandose la macro pero en solo en mi archivo?

    <<>>
    Sub Macro3()

    Dim i As Integer
    Dim NumHojas As Integer
    Application.SheetsInNewWorkbook = 1
    i = 1
    NumHojas = Sheets.Count
    Do Until i > NumHojas
    On Error Resume Next

    Sheets(i).Select
    Cells.Copy
    nombre_hoja = ActiveSheet.Name

    Workbooks.Add

    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste
    Application.DisplayAlerts = False

    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & nombre_hoja, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False

    ActiveWorkbook.Close False
    Application.DisplayAlerts = True
    i = i + 1
    Loop

    End Sub

  6. Buenas noche. Gracias por su articulo.
    Si lo que quiero es que cada archivo se genere a un pdf independiente con el nombre de la hoja que cambio haria?
    Gracias miles si puede ayudarme.

  7. Muchas gracias por el aporte. es de gran utilidad solo me pregunto dos cosas.
    coincido con Erica, si hay manera de que se genere un PDF.

    y la segunda. las pestañas de excel están formuladas. como hacer para que al momento de ejecutar la macros rompa vínculos y queden solo datos.

    de nuevo MUCHÍSIMAS GRACIAS!

  8. Se puede guardar en pdf?

  9. hola, que tal, muy buen aporte, me sirve de mucho pero tengo una consuta, como hago para generar solo una hoja seleccionada, vale decir ponerle un boton de comando y que solo me genere la hoja que estoy trabajando. Desde ya, muchas gracias. Slds.

  10. hola, al ejecutar la macro procesa cada hoja con su respectivo nombre ...eso lo hace excelente pero cuando voy a revisar el archivo la informacion se copio en todos igual y no ranfirio lo que tenia la hoja .. lo que tranfirio es donde esta ubicado...

  11. hola, se puede hacer que aparte de crear el archivo con los datos de la hoja, incluya en ese archivo una hoja adicional? ejemplo, en el archivo original hay 10 hojas y vamos a crear 10 archivos, pero la hoja11 tiene informacion importante, se puede en al macro hacer que cree el achivo y adicional copie la "hoja11" en cada uno, mil gracias

Deja una respuesta

Los campos requeridos estan marcados con *.