jueves, 22 de enero de 2015

VBA: Copiar datos filtrados/visibles de una Tabla.

Veremos hoy un trabajo doble interesante:
Primero veremos cómo aplicar un filtro de fechas con macros sobre una Tabla (problemático habitualmente por una incompatibilidad de la configuración regional)
Segundo cómo copiar y pegar el resultado del filtro aplicado.


Partiremos de la siguiente hoja:

VBA: Copiar datos filtrados/visibles de una Tabla.



El trabajo del siguiente procedimiento consiste primero en aplicar un Filtro de fechas sobre el campo 'Fecha'; para ello deberemos tener la precaución de definir las variables con el tipo Long.
El motivo es salvar la configuración regional enfrentada de nuestra hoja de cálculo (dd/mm/aaaa) y la de VBA (mm/dd/yyyy)... al definirlo como Long (número entero), tratamos la fecha como lo que es para Excel, un número entero (en cualquier configuración regional, un número es un número...).


Una vez conseguido y aplicado el filtro, seleccionaremos sólo las celdas visible empleando el método SpecialCells(xlCellTypeVisible), a partir del cuerpo de la Tabla con la propiedad .DataBodyRange

Insertamos y ejecutamos el siguiente procedimiento, asignado al botón 'EXTRAER':

Sub RangoFecha()
'definimos las variables como Long para poder aplicar el autofiltro
'unica forma de salvar el problema de la configuración regional
Dim FechaDesde As Long, FechaHasta As Long
'definimos variables para aplicar el filtro sobre la tabla
FechaDesde = CDate(Range("B1").Value)
FechaHasta = CDate(Range("B2").Value)

'aplciamos el autofiltro sobre nuestro Tabla
ActiveSheet.ListObjects("TblDatos").Range.AutoFilter _
    Field:=1, Criteria1:=">=" & FechaDesde, Operator:=xlAnd, Criteria2:="<=" & FechaHasta

'aplciamos el copiado del resultado
Dim rng As Range

'trabajamos sólo sobre el cuerpo de la Tabla (.DataBodyRange)
'no sobre cabecera o fila totales
With ActiveSheet.ListObjects("TblDatos").DataBodyRange
    'definimos el rango visible
    'depurando el error por si no hubiera nada filtrado
    On Error Resume Next
    Set rng = .Resize(.Rows.Count, .Columns.Count).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
End With

'controlamos el fallo..
If rng Is Nothing Then
    'si no hubiera nada como resultado del filtro
    MsgBox "Sin datos a copiar"
Else
    'en caso hubiera algún dato
    MsgBox rng.Address                      'mostramos el rango de celdas..
    rng.Copy Destination:=Range("C23")      'copiamos el rango y lo pegamos a partir de C23
    Application.CutCopyMode = False         'liberamos el Portapapeles
End If

'finalizamos quitando el filtro aplicado
ActiveSheet.ListObjects("TblDatos").Range.AutoFilter Field:=1

End Sub

El resultado tras la ejecución de la macro es el siguiente... Tras aplicar el filtro vemos el Cuadro de mensaje que muestra el rango de celdas visibles dentro del cuerpo de la Tabla

VBA: Copiar datos filtrados/visibles de una Tabla.

Y se continuamos la ejecución veremos el copiado y pegado de estos datos:

VBA: Copiar datos filtrados/visibles de una Tabla.

20 comentarios:

  1. Buenas tardes.

    En primer lugar enhorabuena por el blog.
    He intentado poner en práctica esta macro, pero me copia toda la tabla, no me aplica el filtro de las fechas.
    No se que estoy haciendo mal, por lo que agradezco tu ayuda.
    A continuación te indico lo que pongo exactamente:

    Sub RangoFecha()


    Dim FechaDesde As Long, FechaHasta As Long

    FechaDesde = CDate(Range("CU2").Value)
    FechaHasta = CDate(Range("CU3").Value)


    ActiveSheet.ListObjects("Tabla13").Range.AutoFilter _
    Field:=1, Criteria1:=">=" & FechaDesde, Operator:=xlAnd, Criteria2:="<=" & FechaHasta


    Dim rng As Range



    With ActiveSheet.ListObjects("Tabla13").DataBodyRange


    On Error Resume Next
    Set rng = .Resize(.Rows.Count, .Columns.Count).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    End With


    If rng Is Nothing Then

    MsgBox "Sin datos a copiar"
    Else

    MsgBox rng.Address
    rng.Copy Destination:=Range("CZ4")
    Application.CutCopyMode = False
    End If


    ActiveSheet.ListObjects("Tabla13").Range.AutoFilter Field:=1

    End Sub


    Un saludo

    ResponderEliminar
    Respuestas
    1. Hola Diego,
      trabajar con fechas es siempre complicado y tedioso...
      Prueba cambiando esta parte
      FechaDesde = CDate(Range("CU2").Value)
      FechaHasta = CDate(Range("CU3").Value)
      por esta
      FechaDesde = CLng(Range("CU2").Value)
      FechaHasta = CLng(Range("CU3").Value)

      quizá así sí te funcione...
      Saludos

      Eliminar
  2. Hola Ismael,
    Muchas gracias por tu respuesta.
    Apliqué el cambio que me propones, y la primera vez que ejecuté la macro me funcionó, pero al cambiar de fecha, me salta un error "13" en tiempo de ejecución: no coinciden los tipos.

    Te agradezco la ayuda.

    Un saludo

    ResponderEliminar
  3. Hola otra vez,
    He comprobado una cosa:
    En la celda CU2 no tengo escrita la fecha, sino que tengo escrito =AB12 Esto es porque la fecha desde es variable. En este caso es cuando me salta el error.
    Para que no sea así, tengo que escribir la fecha manualmente.
    ¿Cómo puedo resolverlo?

    Un saludo

    ResponderEliminar
    Respuestas
    1. Hola...
      toma la variable directamente desde AB12 y no de CU3
      ;-)
      Saludos

      Eliminar
  4. Hola,
    Gracias por la respuesta. En la celda AB12 tengo una fórmula que es un SI anidado, por lo que esa solución no me sirve.
    Un saludo

    ResponderEliminar
    Respuestas
    1. Hola Diego,
      que la fecha venga de una fórmula o esté introducida sin más no influye (no debe) a la hora de trabajar sobre ella... la solución de trabajar con Clong suele ser suficiente.
      Asegúrate que está bien escrita y que es una fecha en la celda (que no es un texto que parece fecha), quizá al venir de una fórmula anterior esté perdiendo esa propiedad...
      La señal que no es problema de la programación es que cuando la escribes manualmente sí la reconoce.
      Slds

      Eliminar
  5. Buenos dias, Ismael
    Traigo lo siguiente quiero almacena en otras 3 hojas los datos que ingreso en la celda a2 de una hoja si cumple una condicion.
    Ejemplo si en la celda a1 dice 1 el dato que introduzca en la celda a2 se vaya a la hoja 2 y si dice 2 esedato se vaya a la hoja 3 y asi sucesivamente creando una base de datos. tengo el siguiente codigo que solo lo hace a una sola hoja.
    gradezco tus valiosos cconsejos.

    Sub proceso()
    Sheets("Hoja1").Select
    Range("A1").Copy
    Sheets("Hoja2").Range("a65000").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False
    End Sub

    ResponderEliminar
  6. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  7. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  8. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  9. Hola Ismael,
    estoy intentando aplicar tu articulo en un filtro pero me devulve el error de ejecucion 9: "Subindice fuera de rango" No entiendo a que se refiere¿Me puedes echar una mano? ¿Tiene algo que ver con el campo 9?

    Este es el codigo que utilizo:

    Sub filtro()
    Worksheets(1).ListObjects("Table1").Range.AutoFilter field:=9, Criteria1:="Cubierta"
    End Sub

    ¿Y si quisiera aplicar el filtro sobre otro campo adicional?¿otro parametro Field o como?

    Muchas gracias,
    Saludos

    ResponderEliminar
    Respuestas
    1. Hola fede,
      no tiene que ver directamente con que quieras filtrar el noveno campo...
      puede ser que tu tabla no se llame 'Table1' o que no reconozca la hoja..
      Lo mejor que puedes hacer es grabar la acción con el asistente de macros y ver las diferencias.

      Par añadir un segundo filtro, efectivamente, añade una segunda línea:
      Activesheet.ListObjects("Table1").Range.AutoFilter field:=10, Criteria1:="Ok"

      Saludos

      Eliminar
  10. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  11. Hola, quiero leer los primeros 4 valores en la primera columna de un tabla filtrada, pero por separado, ya que quiero escribir el valor en variables.
    Es decir tengo top1, top2, top3 y top4. Y quiero que top1 = primer valor de la tabla filtrada y así sucesivamente, cómo puedo lograr eso?

    ResponderEliminar
    Respuestas
    1. Hola,
      se me ocurre aplicar el filtro (top 4) y leer esos registros con el bucle comentado en este post, según se van leyendo ir asignandolos a las variables top1, top2, etc...

      ¿Es eso lo que pretendes hacer?

      Saludos

      Eliminar
    2. Este comentario ha sido eliminado por el autor.

      Eliminar
    3. Tengo distintos tipos de productos en una tabla en la primera columna y una cantidad de piezas en la segunda columna y en la tercera columna si el producto es nuevo o viejo.
      Yo filtro con VBA de mayor a menor la segunda columna y luego un segundo filtro que sólo muestre los valores mayores a 0 y finalmente un tercer filtro en la tercer columna si quiero ver los nuevos o los viejos.
      Lo que me resulta una lista de al menos 10 productos, y yo solo quiero los primeros 4 de dicha lista, almacenarlos en variables.

      Eliminar
    4. como te decía, si tras aplicar esos filtros (con VBA o manualmente) aplicas el código del post sobre los cuatro primeros, tendrás lo que necesitas...
      Saludos cordiales

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.