VBA Pegado y Pegado Especial

Este tutorial le mostrará cómo utilizar Pegado Especial en VBA para pegar sólo ciertas propiedades de las celdas (ej. valores, formatos)

En Excel, cuando se copia y pega una celda, se copian y pegan todas las propiedades de la celda: valores, formatos, fórmulas, formato numérico, bordes, etc:

En cambio, puedes «Pegar Especial» para pegar sólo ciertas propiedades de la celda. En Excel, se puede acceder al menú Pegar especial con el atajo de teclado CTRL + ALT + V (después de copiar una celda):

 

Aquí puedes ver todas las combinaciones de propiedades de celdas que puedes pegar. Si grabas una macro mientras usa el menú Pegar Especial, puede simplemente usar el código generado. Esta es a menudo la forma más fácil de usar VBA para Pegar Especial.

Pegar valores

Pegar Valores sólo pega el «valor» de la celda. Si la celda contiene una fórmula, Pegar Valores pegará el resultado de la fórmula. Este código copiará y pegará valores para una sola celda en la misma hoja de cálculo:

Range("A1").Copy
Range("B1").PasteSpecial Paste:=xlPasteValues

Copiar y pegar valores en una hoja diferente

Este ejemplo copiará y pegará valores para celdas individuales en diferentes hojas de cálculo

Sheets("Sheet1").Range("A1").Copy
Sheets("Sheet2").Range("B1").PasteSpecial Paste:=xlPasteValues

Estos ejemplos copiarán y pegarán valores para un rango de celdas:

Copiar y pegar valores de rangos

Range("A1:B3").Copy
Range("C1").PasteSpecial Paste:=xlPasteValues

Copiar y pegar valores de columnas

Columns("A").Copy
Columns("B").PasteSpecial Paste:=xlPasteValues

Copiar y pegar valores filas

Rows(1).Copy
Rows(2).PasteSpecial Paste:=xlPasteValues

Pegar valores y formatos numéricos

Al pegar valores sólo se pega el valor de la celda. No se pega ningún formato, incluyendo el formato numérico. A menudo, cuando pega valores, probablemente querrá incluir también el formato numérico para que sus valores permanezcan formateados. Veamos un ejemplo. Aquí pegaremos el valor de una celda que contiene un porcentaje:

 

Sheets("Hoja1").Columns("D").Copy
Sheets("Hoja1").Columns("B").PasteSpecial Paste:=xlPasteValues

Observe cómo se pierde el formato del número de porcentaje y en su lugar se muestra un valor decimal descuidado. En su lugar, utilicemos los formatos Pegar Valores y Números:

Sub copiarValoresNumeros_Y_Formatos()
    Sheets("Hoja1").Columns("D").Copy
    Sheets("Hoja1").Columns("B").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End Sub

Ahora puedes ver que el formato numérico también se pega encima, manteniendo el formato de porcentaje.

.Value en lugar de .Paste

En lugar de pegar los valores, puede utilizar la propiedad Value del objeto Range:

Esto hará que el valor de la celda A2 sea igual al valor de la celda B2

Range("A2").Value = Range("B2").Value

También puede establecer un rango de celdas igual al valor de una sola celda:

Range("A2:C5").Value = Range("A1").Value

o un rango de celdas igual a otro rango de celdas de tamaño idéntico:

Range("B2:D4").Value = Range("A1:C3").Value

Es menos complicado utilizar la propiedad Value. Además, si quieres dominar Excel VBA, deberías estar familiarizado con el trabajo con la propiedad Value de las celdas.

Propiedad Value de la Celda vs proiedad Value2

Técnicamente, es mejor utilizar la propiedad Value2 de una celda. Value2 es ligeramente más rápido (esto sólo importa con cálculos extremadamente grandes) y la propiedad Value puede dar un resultado truncado si la celda está formateada como moneda o fecha. Sin embargo, el 99%+ del código que he visto utiliza .Value y no .Value2. Personalmente no utilizo .Value2, pero debes saber que existe.

Range("A2").Value2 = Range("B2").Value2

Constructor de Copiar y Pegar

Hemos creado un «Constructor de código para copiar y pegar» que facilita la generación de código VBA para copiar (o cortar) y pegar celdas. El constructor es parte de nuestro complemento VBA: AutoMacro. AutoMacro también contiene muchos otros generadores de código, una extensa biblioteca de códigos y potentes herramientas de codificación.

Pegado especial – formatos y fórmulas

Además de Pegar Valores, las opciones más comunes de Pegado Especial son pegar formatos y pegar fórmulas

Pegar formatos

Pegar formatos le permite pegar todo el formato de las celdas.

Range("A1:A10").Copy
Range("B1:B10").PasteSpecial Paste:=xlPasteFormats

Pegar fórmulas

Pegar fórmulas pegará sólo las fórmulas de las celdas. Esto también es extremadamente útil si quiere copiar las fórmulas de las celdas, pero no quiere copiar los colores de fondo de las celdas (u otro formato de celdas).

Range("A1:A10").Copy
Range("B1:B10").PasteSpecial Paste:=xlPasteFormulas

Pegar fórmulas y formatos numéricos

Al igual que en el caso de pegar valores y formatos numéricos, también se pueden copiar y pegar formatos numéricos junto con fórmulas

Aquí copiaremos una fórmula de celda con Formato de Número Contable y Pegar Fórmulas solamente.

Sub pegarFormulasyFormatosNumericos()
    Sheets("Hoja1").Range("D2:D6").Copy
    Sheets("Hoja2").Range("D2").PasteSpecial xlPasteFormulas
End Sub

 

Observa cómo se pierde el formato de los números y en su lugar se muestra un valor descuidado no redondeado. En su lugar, utilicemos los formatos Pegar Fórmulas y Números:

Sub pegarFormulasyFormatosNumericos()
    Sheets("Hoja1").Range("D2:D6").Copy
    Sheets("Hoja2").Range("D2").PasteSpecial xlPasteFormulasAndNumberFormats
End Sub

Ahora puedes ver que el formato de los números también se pega encima, manteniendo el formato de Contabilidad.

Pegado especial – transponer y omitir espacios en blanco

Pegado especial – transponer

El pegado especial transponer le permite copiar y pegar celdas cambiando la orientación de arriba-abajo a izquierda-derecha (o viceversa):

Sub pegadoEspecialTransponer()
  Sheets("Hoja1").Range("A1:A5").Copy
  Sheets("Hoja1").Range("B1").PasteSpecial Transpose:=True
End Sub

Pegado especial – omitir espacios en blanco

Omitir espacios en blanco es una opción de pegado especial que no parece usarse tan a menudo como debería. Le permite copiar sólo las celdas que no están en blanco al copiar y pegar. Así que las celdas en blanco no se copian. En este ejemplo de abajo. Copiaremos la columna A, haremos un pegado normal en la columna B y omitiremos el pegado de los espacios en blanco en la columna C. Puedes ver que las celdas en blanco no fueron pegadas en la columna C en la imagen de abajo.

Sub pegadoEspecialOmitirBlancos()
    Sheets("Hoja1").Range("A1:A5").Copy
    Sheets("Hoja1").Range("B1").PasteSpecial SkipBlanks:=False
    Sheets("Hoja1").Range("C1").PasteSpecial SkipBlanks:=True
End Sub

Otras opciones de pegado especial

Pegado especial – comentarios

Sub pegadoEspecialComentarios()
    Sheets("Hoja1").Range("A1").Copy
    Sheets("Hoja1").Range("E1").PasteSpecial xlPasteComments
End Sub

 

Pegado especial – validación

 

Sub pegadoEspecialValidacion()
    Sheets("Hoja1").Range("A1:A4").Copy
    Sheets("Hoja1").Range("B1:B4").PasteSpecial xlPasteValidation
End Sub

 

Pegado especial – todo usando el tema de origen

 

Sub pegadoEspecialTodoUsandoTemaOrigen()
    Workbooks(1).Sheets("Hoja1").Range("A1:A2").Copy
    Workbooks(2).Sheets("Hoja1").Range("A1").PasteSpecial
    Workbooks(2).Sheets("Hoja1").Range("B1").PasteSpecial xlPasteAllUsingSourceTheme
End Sub

Pegado especial – todo excepto los bordes

Range("B2:C3").Copy
Range("E2").PasteSpecial
Range("H2").PasteSpecial xlPasteAllExceptBorders

 

Pegado especial – anchos de Columna

Uno de mis favoritos. PasteSpecial Anchos de Columna copiará y pegará el ancho de las columnas.

Sub pegadoEspecialAnchosdeColumna()
Range("A1:A2").Copy
Range("C1").PasteSpecial
Range("E1").PasteSpecial xlPasteColumnWidths
End Sub

 

Pegado especial – Todos los formatos condicionales de combinación

 

Sub pegadoEspecialFormatosCondicionalesCombinacion()
Range("A1:A4").Copy
Range("C1").PasteSpecial
Range("E1").PasteSpecial xlPasteAllMergingConditionalFormats
End Sub