Material registrado:

    Safe Creative #1709190281730

    Cajón de sastre:

    Copiar un rango a varias hojas de Excel

    Planteamiento

    Este es un buen ejemplo de operación repetitiva ideal para ser realizada por una macro. Pongamos por caso que tenemos que copiar el rango “B1:G5” de la Hoja1, en la misma zona del resto de hojas de un archivo. La solución manual de copiar el rango, y luego ir seleccionando cada hoja, dentro de la hoja la celda B1, y pegar lo copiado cada vez, solo es admisible si el número de hojas de destino es de unas pocas. Pero en el caso real en el que se inspira este ejemplo, el número de hojas de destino era 220.

    Solución rápida

    Grabamos una macro haciendo la tarea para una cualquiera de las hojas de destino. El código generado es el siguiente:

    Sub Macro1()
        Hoja1.Range("B1:G5").Select
        Selection.Copy
        Sheets("Hoja2").Select
        Range("B1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End Sub

    Generalizamos el código y lo ponemos dentro de una estructura iterativa para que recorra todas las hojas, salvo la hoja de origen, y realice la misma operación.

    Sub CopiarRangoEnHojas()
        Dim i As Long
            Hoja1.Activate
            Hoja1.Range("B1:G5").Select
            Selection.Copy
        For i = 2 To Worksheets.Count
            'Estructura iterativa
            Worksheets(i).Select
            Range("B1").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Next i
    End Sub

    Hemos puesto la selección inicial del rango a copiar fuera de la estructura iterativa, pues basta con cargarlo en memoria una sola vez.

    Para realizar la iteración a través de todo el conjunto de hojas, aprovechamos que existe una colección de objetos en Excel llamada WorkSheets, y que a través de un índice entre paréntesis nos podemos referir a cada uno de los objetos de esa colección, es decir, a cada una de las hojas. De esta manera, será suficiente con empezar en el índice número 2, ya que estamos suponiendo que el rango de origen está en la hoja de índice número 1, para poder recorrer toda la colección hasta su último elemento, que no tenemos que preocuparnos de dar de forma explícita, sino que se calcula automáticamente con la propiedad count de la colección.

    Un inconveniente leve de esta solución es que para realizar la operación inicial de copia del rango y luego para realizar cada una de las operaciones de pegado, tenemos que seleccionar fehacientemente la hoja en cuestión, ya sea a través del método Activate o del Select. En Excel es necesario que para seleccionar cualquier elemento de una hoja, la hoja esté activa. Y como estamos usando el método Select, pues no hay mas remedio que ir activando la hoja en cuestión cada vez.

    Imagen del código en acción
    El código VBA en acción

    Solución mejorada

    La solución anterior es eficaz y resolutiva. Eso no se pone en duda. Como primera aproximación para quitarnos el problema de en medio es más que suficiente.

    Ahora bien. Es cierto que en el caso de muchas hojas, la selección de la hoja ralentice la tarea más de lo deseable y tengamos que esperar un rato viendo unos parpadeos de pantalla algo molestos. También es cierto que la solución deja margen para la optimización de acuerdo a las reglas del lenguaje VBA.

    En concreto, podemos aprovechar que:

    Para cargar algo en memoria no es imprescindible seleccionarlo antes, siempre que nos podamos referir al objeto claramente y que el método Copy le sea aplicable.

    El método Paste puede obviarse en muchos casos, particularmente con los valores contenidos en celdas de la hoja, y cambiarse por el operador de asignación “=”, que es mucho más eficaz. Esto significa que nos podríamos ahorrar todas esas selecciones de hoja que causan el parpadeo de pantalla en el caso de muchas hojas.

    Con estas cosas en mente, podemos llegar al siguiente código:

    Sub CopiarAlRestoDeHojas()
        Dim RangoParaCopiar As Range
        Set RangoParaCopiar = Hoja1.Range("B1:G5")
        Dim hoja As Worksheet
        For Each hoja In Worksheets
            If hoja.Name <> "Hoja1" Then   
                hoja.Range("B1:G5").Value = RangoParaCopiar.Value  
            End If
        Next hoja
    End Sub

    Observemos que ni siquiera es necesaria la operación Copy para el rango inicial. Simplemente lo asignamos a un rango declarado como objeto Range, y luego el bucle realiza iterativamente operaciones de asignación de rango.

    Con esta solución también muestro cómo excluir la hoja original a través de la condición IF que se refiere a su nombre, que creo que es lo más fácil, pero podríamos hacerlo si conocemos el índice.

    En caso de no poder ver el video, haz click aquí: Copiar rango a varias hojas de Excel

    Comments

    This post currently has 7 responses

    • Disculpe hay un vídeo, en el que me estoy apoyando, es el que elimina filas si dos columnas son cero, yo solo lo ocupo con una columna, pero cuando existen 2 valores seguidos que sean cero, solo elimina la primera fila, la segunda ya no la elimina, ojalá pueda apoyarme, igual solo quiero que lo haga en una seleccion de 6 filas y 4 columnas que inserto grabando una macro, gracias, ojalá pueda apoyarme.

    • Hola el macro funciono genial, pero me pega como valores y yo necesto que copie y pegue tal cual el rango en la Hoja1 al resto de las Hojas
      Pegue el macro como lo tengo para que puedas ver que me falto.
      Gracias
      Sub CopiarAlRestoDeHojas()
      Dim RangoParaCopiar As Range
      Set RangoParaCopiar = Hoja1.Range(«a1:ak553»)
      Dim hoja As Worksheet
      For Each hoja In Worksheets
      If hoja.Name «Principal» And hoja.Name «Base» And hoja.Name «Correspondencia» And hoja.Name «Materias» And hoja.Name «Usuarios2» And hoja.Name «Hoja1» Then
      hoja.Range(«a1:ak553»).Value = RangoParaCopiar.Value
      End If
      Next hoja
      End Sub

      • Hola. Tal y como lo tienes bastaría con cambiar la línea que dice:
        hoja.Range(«a1:ak553»).Value = RangoParaCopiar.Value
        por:
        hoja.Range(«a1:ak553»).Formula= RangoParaCopiar.Formula
        Saludos desde Madrid

    • Estimado Sr. Eloy
      Me gustaria saber, si me puede hacer una pequeña aplicación de lotto de 6/30 en excel, tengo el model diseñado en excel, pero no se como enviarselo a Usted, por supuesto despues de enviarselo me gustaria que Usted me diera un presupuesto ( Utilizo normalemente PayPal) puesto que vivo en Lithuania.
      Muchas Gracias anticipadamente.
      Un saludo
      Nota : Mis felicitaciones por sitio web inspirador y maravilloso.

    • Buenas!
      Tengo un inconveniente que me urge solucionar…
      Tengo un libro excel con la siguiente composición…
      Hoja 1 Listado de 900 items cada uno con un precio diferente…
      Luego tengo 900 hojas las cuales serían el desglose de cada Item.
      Necesito poder distribuir los datos de cada item del listado principal a cada hoja de desglose…
      Desde ya se agracede la sugerencia que puedas brindarme…
      Saludos Cordiales

      • Parece un trabajo de consultoría. Yo he colgado los trastos de consultor de Excel hace unos meses, pero si me pones el material en una carpeta compartida en la nube y me pones el enlace aquí, veré si te puedo ayudar.

    Deja una respuesta

    Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

    Sidebar



    Si continuas utilizando este sitio, significa que aceptas el uso de cookies. más información

    Los ajustes de cookies de esta web están configurados para "permitir cookies" y así ofrecerte la mejor experiencia de navegación posible. Si sigues utilizando esta web sin cambiar tus ajustes de cookies o haces clic en "Aceptar" estarás dando tu consentimiento a esto.

    Cerrar