Abra el archivo "Macros.xlsm" haciendo clic aquí

Vaya a la hoja Datos.

En esta hoja tenemos una lista de participantes a un evento internacional en España, desarrollado en varias cedes.

Vamos a realizar un filtro avanzado para extraer a todos los participantes que cumplen ciertos criterios
En la hoja Macro 04, ya contamos con el rango de criterios: A2:E3.

Según la hoja "Sesión de macros", debemos grabar una macro que realice este filtro avanzado.

Puesto que los datos están en una hoja, el rango de criterios en otra y el área de salida puede estar también en otra hoja,
vamos a usar nombres de rango en lugar de rangos. Sólo por simplicidad, el rango de salida empezará en A10 de la hoja del rango de criterios

En la hoja Datos: Seleccione el rango A2:E498 (observen que para este tipo de operaciones el rango debe incluir la cabecera de columna),
usando [Fórmulas] - [Nombres definidos] - [Asignar nombre] . En nombre digitar: DatosEv.
Ahora vamos a la hoja Macro 04. Seleccionemos el rango A2:E3, usando la secuencia anterior, le asignamos el nombre CritEv.

Ya estamos listos para iniciar la grabación de la macro llamada Macro04, con método abreviado [CTRL]+[j].

Empecemos:

Hacer clic en A10 de esta hoja.
[Vista] - [Macros] - [Grabar macro ...]
En nombre: Macro04
En método abreviado: j
[Aceptar]
Usamos la secuencia: [Datos] - [En Ordenar y filtrar] - [Avanzadas]
En la ventana que emerge: Activar: [Copiar a otro lugar]
Rango de la lista: DatosEv
Rango de criterios: CritEv
Copiar a: Ya está seleccionado el rango A10:A10. Si no lo estuviera digitamos A10.
Detener grabación.

Antes de ejecutar la macro y para que al hacerlo no falle, vamos a grabar otro que elimine todas las filas rellenadas.
Para elo, hacemos clic en la etiqueta de fila [10], seleccionamos hasta la última fila con datos, usamos botón derecho y seleccionamos la opción [Eliminar]

Nota:
Si quisiéramos ejecutar la macro, la macro fallaría pues al querer extraer sobre A10, estando ocupada, emitiría un error.

Grabación de la macro para eliminar las filas:

[Vista] - [Macros] - [Grabar macro ...]
En nombre: Macro04Borrar
En método abreviado: a
[Aceptar]
Clic en la etiqueta 10 de dicha fila
Como no sabemos cuántas filas son, usamos:
Presionamos [Shift]. Teniendo presionada esta tecla, una vez clic en la tecla [Fin] y una vez clic en [Flecha abajo].
Ahora, usando el botón derecho, seleccionar [Eliminar]
Detener grabación

Ahora ya puede borrar la extracción, borrarla usando [CTRL]+[a] y [CTRL]+[a]. etc.

Preguntas interesantes:
¿Qué sucede si, además de ser participantes en la sede de Barcelona quisiéramos extraer a los que tienen reserva y del idioma I ?
En este caso es suficiente ingresar en C3: SI  y en D3: I (así con mayúscula pues así están los datos).

El problema es, si ahora quisiéramos un reporte de los participantes que no tenen reserva tanto de Valladolid como de Madrid.

Solución de este caso:
En E3: VALLADOLID. En C3: NO
En E4: MADRID. En C4: NO
Eliminamos el rango de criterio usando: [Fórmulas] - [Nombres definidos] - [Administrar nombres] - Seleccionar CritEv, {Eliminar] - [Cerrar]
Ahora, seleccionar el nuevo rango de criterio: A2:E4.
Usar: [Fórmulas] - [Nombres definidos] - [Asignar nombre] . En nombre digitar: DatosEv.

Ahora vuelva a ejecutar la macro sobre A10 vacía y la macro a para borrar la extracción.

Claro, si ahora quiere un reporte sólo los de MADRID, tendrá que realizar los ajustes que hemos hecho, lo cual resulta un fastidio. Si pudiéramos modificar la macro, sería formidable.

Guarde su archivo con el mismo nombre y habilitado para macros.

Continuemos con la hoja "Macro04 con Otros datos".

Vayamos a la hoja "Otros datos".

En esta hoja tenemos un listado de nombres de películas en el cual se tiene registrado el tipo, estado, precio de alquiler, etc.

Se desea obtener un reporte mediante el filtro avanzado bajo los criterios especificados en la hoja "Macro04 con otros datos".

En el ejercicio 7 de la hoja "Sesión de macros" se pide aplicar la macro Macro04 para estos nuevos datos y con el rango de criterios que se muestran.
 

Solución

Puesto que Macro04 define al rango de datos como DatosEv y el rango de criterios como CritEv, debemos eliminar cambiar el rango para cada uno de ellos.
Primero eliminamos los dos nombres de rango: [Fórmulas] - [Nombres definidos] - [Administrador nombres]
Seleccionar "CritEv" y luego clic en [Eliminar] - Confirmar que se desea eliminar
Seleccionar "DatosEv" y luego clic en [Eliminar]. Confirmar que se desea eliminar. Luego [Cerrar]

Pasar a la hoja "Otros datos".
Seleccionar el rango A1:H787, usar [Fórmulas] - etc para asignarle el nombre DatosEv.
Pasar a la hoja "Macro04 con otros datos". Seleccionar el rango A1:H5 y darle el nombre CritEv.

Estando en esta última hoja, ya puede usar [CTRL]+[j] y [CTRL+[a].

Naturalemente si desea usarlas con los datos anteriores, deberá eliminar la definición de los dos rangos y definirlos con los anteriores, siempre usando los mismos nombres.

Cuando sepamos VBA leeremos desde el teclado el nombre de los rangos de datos y de criterio.

Guarde su libro con el mismo nombre, ya está habilitado para guardar las nuevas macros.


Tabla dinámica con macro

Las dos siguientes macros que vamos a crear, nos permitirá generar una tabla dinámica base a partir de la cual, se podrá modificar la presentación de la misma.

Pasemos a la hoja "Macro 05".
Antes de grabar nueva macro, hagamos una tabla dinámica con los datos de esta hoja.
Use la siguiente secuencia:
Clic al interior de la tabla. Observe que la columna A no debe ser tomada en cuenta. El rango de datos será B10:J361.
[Insertar] - [Tabla dinámica]. Verificar que el rango de datos sea el correcto.
Observe también que está activada la opción: "Nueva hoja de cálculo". Aunque podemos seleccionar "Hoja de cálculo existente, en cuyo caso debemos ingresar la Hoja y celda inicial.
[Aceptar]
Observe que nos ha añadido nueva hoja y allí se construirá la tabla dinámica.
En la lista de campos, haga clic en "Tienda", clic en "FormaPago", clic en "Cantidad", clic en "Monto tootal.
Finalmente, clic en otra celda fuera de la tabla.

Esto es lo que va a hacer la macro. Insertará nueva hoja o usará la hoja que le demos de dato.
Cada vez que ejecutemos la macro, ésta insertará nueva hoja o usará la hoja ya le dimos como dato.
Pero si se inserta nueva hoja, ésta va a tener un nuevo nombre diferente a la que se creó al inicio. Si fue creado "Hoja1", al ejecutar la macro creará "Hoja2".

Sin embargo, la macro reclamará "Hoja1" pues en ella insertó los campos de la tabla. En consecuencia, la macro fallará. Y cada vez que se ejecute, añadirá otra hoja y buscará "Hoja1".

Del mismo modo, si al comienzo le dimos nombre de hoja existente, al ejecutar la macro, querrá hacer la tabla encima de una que ya existe, en consecuencia la macro fallará.

¿Cómo salimos de esto?

Lo de la hoja nueva, no va.
Usaremos la segunda opción, lo de la hoja existente. Sólo que, cada vez que querramos usar la macro, la hoja existente debe estar vacía.
Esto significa que, si la hoja existente contiene datos que sirven, debemos cambiarla de nombre, añadir otra y a ésta darle el nombre con el cual trabaja la macro. Y listo.
 

Empezamos

Usando botón derecho, inserte nueva hoja hoja de cálculo, de preferencia al lado de la hoja "Macro 05". Si es el caso lo deberá mover a su costado.
Cambie su nombre por "Tempo" (nombre cualquiera).
Vamos a la macro: Vamos a la hoja Macro 05", clic al interior de los datos.
[Vista] - [Macros] - [Grabar nueva macro ...] - Nombre: TablaDin. Méodo abreviado: [k]
[Insertar] - [Tabla dinámica]. Verifique que el rango sea el correcto
Seleccionar [Hoja de cálculo existente]. En [ubicación] hacer clic en la flecha.
Clic en la hoja "Tempo" y luego clic en la celda B2. No en la fila 1 y no la columna A(puede servir para otros propósitos).
Presionar [Intro], cuando vuelva a la ventana anterior, clic en [Aceptar]
En la lista de campos, clic en [Tienda], clic en [Forma Pago], clic en [Cantidad], clic en [Monto Total]
Clic fuera de la tabla.
Detener grabación.

En la hoja "Tempo". Clic en el botón anterior a la etiqueta de fila [1] y a la izquierda de la etiqueta de columna A, para seleccionar toda la hoja
Botón derecho, [Eliminar]. Ya tiene la hoja vacía.
Vaya a la hoja de datos "Macro 05".
Ejecute la macro usando [CTRL]+[k]
Borre o cambie de nombre a esta hoja, agregue otra y déle por nombre: Tempo.
Vuelva a ejecutar la macro.

Ejercicio

Dejamos como ejercicio las dos últimas macros.
En la hoja "Macro 06", debe agregar otra hoja, darle cualquier nombre y grabar la macro. Nombre Macro06. No tendrá método abreviado.
¿Cómo se ejecutará la macro que no tiene método abreviado?
Para ello debe usar: [Vista] - [Macros] - [Ver macros] - Seleccionar Macro06 y listo.
Ya veremos otras formas de ejecutar una macro si no deseamos usar método abreviado.

Y la última macro debe usar la herramienta de Consolidación.

Continuaremos con macros en la siguiente sesión