Estadísticas de Google Analytics

    generado por GADWP 

    Material registrado:

    Safe Creative #1709190281730

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

So empty here ... leave a comment!

Deja un comentario

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