Copiar filas de una tabla filtrada en Excel con Vba (macros)

Numerador automático para facturas es una de las páginas más populares de este blog. Una de las consultas más frecuentes es cómo hacer para guardar copias de las facturas, así que, después de varios años (la nota es del 2008), decidí rehacer el modelo que incluye, además de corregir varios bugs, la posibilidad de manejar los datos de las facturas en una base de datos.
Toda esta introducción viene a cuento de que una de las rutinas que tuve que desarrollar para el nuevo modelo, que estaré publicando en breve, se basa en filtrar las filas de la base de datos y copiarlas a otra hoja (lo hoja que contiene la copia de la factura).
En esta nota mostraré dos métodos eficientes para hacerlo. Como ejemplo utilizaremos los datos de ventas de la base de datos Northwind

Dado que los métodos de Excel, como Autofiltro, suelen ser más eficientes que el código que podamos escribir, podemos grabar las acciones y luego mejorar el código reemplazando, por ejemplo, las referencias a rangos por variables.
Supongamos que queremos copiar a otra hoja todas las ventas a la Argentina. Después de aplicar Autofiltro, grabamos las acciones para el copiado obteniendo este código

Sub Macro1()

'
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Hoja2").Select
    ActiveSheet.Paste
End Sub

Hacemos dos mejoras a este código

• usamos la propiedad CurrentRegion de Range en lugar de Selection.End(xlToRight) y Selection.End(xlDown),

• eliminamos los Select abreviando el código

Sub Macro2()
'
    Selection.CurrentRegion.Copy
    Sheets("Hoja2").Paste
 
End Sub


Pero existe una posibildad más sencillla usando AutoFilter.Range

Sub Macro3()

    ActiveSheet.AutoFilter.Range.Copy
    Sheets("Hoja2").Paste

End Sub

La ventaja de usar AutoFilter.Range es que funciona aún si la celda activa no pertenece a la tabla filtrada.

El error "demasiados formatos de celdas" en Excel

Todo quien haya trabajado suficiente tiempo con Excel se ha topado en alguna oportunidad con el error "demasiados formatos de celda diferentes" (too many different cell formats)

o, en Excel 2003, con

Excel ha encontrado un error y se tuvieron que quitar parte del formato para evitar dañar el libro

o, en Excel 2007-2013, con

Excel encontró contenido no legible en el archivo 

 Otros síntomas ligados a este error pueden ser:

• al abrir un archivo se elimina todo el formato;
• incremento en el tamaño del archivo después de copiar-pegar entre libros
• imposibilidad de pegar lo que se ha copiado al portapapeles (aparece el mensaje "Microsoft Excel no puede pegar datos")

De acuerdo a la base de datos de conocimientos de Microsoft:

Este problema se produce cuando el libro contiene más de aproximadamente 4.000 combinaciones diferentes de formatos de celda en Excel 2003 o 64.000 en Excel 2007 y posteriores. Una combinación se define como un conjunto de elementos que se aplican a una celda de formato único. Una combinación incluye todo el formato de fuente (por ejemplo: tipo de letra, tamaño de fuente, cursiva, negrita y subrayado), bordes (por ejemplo: ubicación, grosor y color), los patrones de celdas, formato, alineación y protección de celda de número.

Hay todo tipo de medidas que pueden y deben tomarse para evitar este problema. Pero si nos topamos con él, la mejor herramienta es XLStylesTool. Esta herramienta, que se puede descargar en forma gratuita,  fue desarrollada por Sergei Gundorov, quien se merece todo nuestro agradecimiento.

Uso de Tablas en Excel - referencias estructuradas

El tema del uso de Tablas (Listas en Excel 2003) no es nuevo en este blog (un clic en la etiqueta Tablas/Listas en la nube de etiquetas les mostrará todas las notas). En mi opinión es una de las mejores innovaciones en Excel en los últimos años. Sin embargo no creo equivocarme si digo que relativamente pocos usuarios sacan provecho de esta herramienta.
Un posible motivo para esta reticencia sea la nueva sintaxis empleada para referenciar las celdas y rangos dentro de la tabla. Al principio esta sintaxis parece un tanto críptica para el usuario habituado a las referencias clásicas "columna/fila" (por ejemplo, A1).

Consideremos esta tabla de ventas por sucursal y zona

En la celda G4 usamos esta fórmula para extraer de la tabla las ventas de cierta zona

=SUMAR.SI($C$3:$C$12,G3,$D$3:$D$12)

Ahora convirtamos esta tabla en  una Tabla (cuando uso tabla con minúscula me refiero a un rango normal de datos) y veamos cómo aparecen las referencias en la fórmula

La fórmula aparece ahora así

=SUMAR.SI(TablaVentas[Zona],G3,TablaVentas[Ventas])

La sintaxis es evidente:

"TablaVentas" se refiere al nombre de la tabla

[Zona] es la referencia al campo (columna) que define la zona de cada sucursal; de la misma manera [Ventas] se refiere a la columna de las ventas.

Para referirnos a otros elementos de la tabla, como la fila de los encabezamientos, usamos distintas formas que resumo en esta tabla

Objeto	Sintaxis
Referencia a la tabla	=TablaVentas
La celda de la fila en la columna	=[@Ventas]
Fila de encabezados	TablaVentas[#Encabezados]
Toda la tabla	=SUMA(TablaVentas[#Todo])
Fila de totales	=TablaVentas[[#Totales],[Ventas]]

Una ventaja evidente de esta sintaxis es la claridad. No hace falta ver el rango referenciado para saber qué calcula la fórmula.
Esto nos permite crear fórmulas con mucha facilidad, como podemos ver

Al comenzar a escribir la fórmula Excel nos muestra las tablas como un rango definido; pero la mejor parte viene ahora

 Al poner el "["Excel nos muestra todos los rangos disponibles para nuestra fórmula. Así que si queremos calcular el total de ventas elegimos Ventas, ponemos el "]" para cerrar la definición del campo, cerramos el o los paréntesis necesarios y apretamos Enter. 
Para referirse a una celda dentro de la Tabla, Excel usa la notación [@nombre del campo]. Por ejemplo, si tenemos una columna de descuentos en nuestra tabla y queremos calcular las ventas neto

Más sobre la sintaxis y el uso de referencias  estructuradas pueden leer en esta nota del sitio Office de Microsoft.