Tiene un libro de trabajo con muchas hojas de trabajo. Desea enviar cada hoja de trabajo a una persona diferente. Hoy, una macro para dividir esos datos.
Ver video
- Joe + Others está buscando una forma de guardar cada hoja de trabajo en un archivo diferente
- Útil para Power Query o después de usar Mostrar páginas de filtro de informe
Transcripción del video
Aprenda Excel de Podcast, Episodio 2107: divida cada hoja de trabajo en un nuevo libro de trabajo
Oye, bienvenido de nuevo. Soy y netcast. Soy Bill Jelen.
Sabía en el fondo de mi cabeza que necesitaba hacer esto durante mucho tiempo, pero dos podcasts recientes realmente lo llevaron, lo llevaron a casa.
Recientemente, en el episodio 2106, estábamos creando un PDF a partir de todas las combinaciones de Slicer. Al final de ese episodio, mostré un método alternativo en el que creamos muchos informes dinámicos, pero los coloca a todos en el mismo Libro de trabajo y recibí un correo electrónico de Joe en California que dice bueno, mira, necesito enviar cada Hoja de trabajo a un cliente diferente, y Lo mismo, en mis seminarios de Power Excel en vivo donde muestro ese truco, la gente dice, bueno no, no lo queremos todo en el mismo libro de trabajo, lo queremos por separado y luego probablemente incluso más importante que eso, está de vuelta en episodio 2077, donde hablé sobre cómo Power Query ahora tiene la capacidad de combinar todos los archivos de Excel en una carpeta, ¿verdad? Y esto es milagroso. Funciona muy bien. Si tenía 400 archivos de Excel, cada uno con una sola hoja de trabajo, tomará todos esos datos de todas esas hojas de trabajo y los pondrá en una cuadrícula.Lo cual es genial, pero si tuviéramos casi el mismo problema. ¿Un libro de trabajo con 400 hojas de trabajo? No puede hacerlo, ¿verdad? No puede lidiar con eso, todavía. Bien, ahora mismo, 1 de julio de 2017, no puede lidiar con eso. Tal vez en seis meses pueda lidiar con eso, pero ahora, tiene que ser libros de trabajo de una sola hoja.
Por lo tanto, necesitamos una forma de poder dividir las cosas en archivos individuales. Bien, vamos a configurar esto. Tenemos el Libro de trabajo que hice en 2106 donde tenemos los datos y luego la Tabla dinámica original y entramos en Analizar, Opciones, Mostrar páginas de filtro de informe y mostrar las páginas de la clave, y crea un montón de Hojas de trabajo diferentes para mí y Quiero tomar esas hojas de trabajo y crear cada una en un archivo separado, pero aunque tenemos eso, hay algunas cosas como Sheet2 y Data que no quiero dividir.
¿Bien? Y, por supuesto, para cada persona, esas cosas, esas, esa lista de hojas de trabajo, que no queremos dividir, será diferente, pero supongo que casi todos tienen algunas hojas de trabajo que no tienen. No quiero dividir.
Muy bien, aquí está la utilidad que podrás descargar. El divisor de hojas de trabajo y aquí tengo una sección en la columna B y es realmente lo único en la columna B donde puede enumerar las hojas de trabajo que no desea dividir. Pueden ser más de dos. Puede completar tantos aquí como desee. Puede insertar nuevas filas y mi manera barata, no quería tener que recorrerlas en la Macro, hasta ahora fuera de su vista aquí, tengo un lugar donde la Macro puede escribir el nombre de la hoja de trabajo actual y luego un simple y pequeña VLOOKUP. Dice, busque esta hoja de trabajo en la que estamos trabajando en este momento, vea si se terminó en la columna B y, si es así, sabremos que es una que no queremos exportar.
Muy bien y de nuevo solo para hacer esto lo más genérico posible, tengo varios rangos con nombre aquí, mi Ruta, mi Prefijo, mi Sufijo, mi Tipo y mi Pasta. Muy bien, entonces averigua dónde quieres que vayan estas cosas. c: Informes . Quiero que cada archivo tenga el nombre de la hoja, pero antes del nombre de la hoja, voy a poner el prefijo de WB, el sufijo de archivo y nada y luego tienes una opción aquí: PDF o XLSX.
Entonces, comenzaremos con XLSX, hablaremos sobre estos valores de pegado antes de guardarlos más tarde. Muy bien y ahora mismo esta es la versión del 1 de julio de 2017, la primera. Si mejoramos esto, simplemente lo reemplazaré en la página web y podrás encontrar la página web en las descripciones de YouTube. Muy bien, así es como va a funcionar. Es un archivo XLSM. Por lo tanto, debe asegurarse de que las macros estén permitidas. alt = "" T, M, S, por seguridad debes estar al menos en este nivel o por debajo. Justo si está en la parte superior, debe cambiar, cerrar el Libro de trabajo, volver a abrir. Cuando abra el Libro de trabajo, dirá, oye, ¿está dispuesto a aceptar las Macros aquí y no es una macro grande en absoluto: sesenta y ocho líneas de código y mucho de eso solo trata de sacar los valores del Menú Sábana,cuáles son las variables ahora.
Sin embargo, lo importante aquí es que funcionará en ActiveWorkbook. Entonces, cambiará al Libro de trabajo que tiene los datos y luego presionará CTRL SHIFT S para ejecutarlo y detectará el ActiveWorkbook y ese será el que dividirá. Agarra ("MyPath") y es solo porque siempre me olvido de poner esa barra invertida, si el último carácter no es una barra invertida, entonces voy a agregar una barra invertida y aquí abajo este es el trabajo real.
Para cada Hoja de trabajo, en el original, en la WBO activa. Hojas de trabajo, vamos a probar para ver si hay una que está allí y la columna B. Si es, si no lo es, entonces vamos a Exportar esta hoja y yo amo esta línea de código. WS.copy dice, cuando tomo este Libro de trabajo, esta Hoja de trabajo, de este gran Libro de trabajo con, usted sabe 20 o 400 Hojas de trabajo y vamos a WS.copy, que hace una copia y lo mueve a un nuevo Libro de trabajo y sabemos, sabemos que ese nuevo Libro de trabajo ahora se convertirá en el Libro de trabajo activo en la Macro y, por supuesto, solo hay una hoja en ese Libro de trabajo y esa hoja es la hoja activa.
Bien, aquí puedo averiguar el nombre del Libro de trabajo. Configúrelo, aplique a esta variable de objeto, libro de trabajo nuevo, hoja de trabajo nueva y luego, cuando tenga que cerrar, puedo hacer WBN.close después de haber hecho el trabajo. Averiguamos el nuevo nombre de archivo usando todas las variables. Elimine ese archivo, si ya existe y luego, si es un archivo de Excel, lo guardamos como, si es un PDF.
Y, por cierto, este código PDF solo funciona en Windows, si está en una Mac, lo siento, tendrá que ir a otro lugar para encontrar el código Mac equivalente. No tengo una Mac. Sé que hay una forma de guardar un PDF en una Mac. Sé que el código es diferente. Tendrá que resolverlo o volver al Excel real en Windows y luego terminamos, cerramos.
Muy bien, es solo una pequeña macro como esa, cambie a nuestro Libro de trabajo de datos aquí, el que tiene todas las Hojas de trabajo. Aquí hay 20 hojas de trabajo diferentes, más las dos que no quiero hacer y luego CTRL SHIFT S así y lo veremos parpadear mientras crea cada una. Ahí estamos: 21 archivos creados.
Echemos un vistazo en el Explorador de Windows y aquí está mi sistema operativo (C :) Informes, que creó para cada hoja de trabajo, nombrado en el libro de trabajo original que creó una nueva versión con WB al principio. Muy bien, Joe, cuando Joe me envió esta nota, dijo que iba a enviar estos datos a los clientes y al principio me asusté porque dije, espera un segundo Joe, vamos a tener un problema porque tú vas a enviar a Gary, sus datos, ¿verdad? Pero esto es, ah, ya sabes, un conjunto de datos en vivo, es una tabla dinámica en vivo. Con todas las cosas aquí, es posible que pueda obtener toda la información para otros clientes de esa manera, ¿verdad? Vaya, no quieres enviarle a un cliente A la información de todos los demás clientes. Eso podría ser una molestia y, de hecho, cuando releí la nota del programa, él era más inteligente que yo, porque dijo:Quiero crearlos como archivos PDF. Yo estaba como, está bien, bueno, entonces sí, no tenemos que preocuparnos por los archivos PDF, está bien, pero lo que agregué aquí, a la macro, fue la capacidad de decir Pegar valores antes de guardar. CIERTO.
Así que lo establece en TRUE y eso va a invocar este pequeño fragmento de código aquí, donde decimos, If PasteV Then the UsedRange.Copy y luego UsedRange.PasteSpecial (xlPasteValues), UsedRange, en lugar de copiar y pegar las 17 mil millones de celdas , lo limitará bien, el UsedRange.
Muy bien, regresemos, cambiemos la hoja de trabajo que tiene los datos, CTRL SHIFT S para dividir y luego esta nueva versión en el directorio de informes, verá que se ha deshecho de la tabla dinámica y ha dejado solo los datos allí. De esa manera, no pueden acceder a todos los datos.
Muy bien, probaremos la otra función. Intentaremos si cambiamos de Excel a PDF cambiar el prefijo a PDFFileOf, lo que queramos allí. Ni siquiera intentaré el sufijo, algo. Muy bien y luego cambie a los datos, CTRL SHIFT S. Muy bien, entonces obtenemos los mismos archivos PDFFileOf el nombre de la hoja de trabajo, algo de PDF y deberíamos tener pequeños PDF agradables allí, así.
Muy bien, ahí lo tienes the.com Worksheet Splitter. Con suerte, lo suficientemente genérico, para lo que necesite. Descárgalo de nuevo desde el enlace que hay en los comentarios de YouTube. Para obtener más información sobre VBA, consulte este libro Excel 2016 VBA y Macros por mí y Tracy? 08: 50.640. Haga clic en I en la esquina superior derecha para leer más sobre el libro.
Joe, de California, y muchos otros han pedido una forma de guardar cada hoja de trabajo en un archivo diferente, ya sea como PDF en el caso de Joe o como archivo Excel en caso de que vaya a utilizar Power Query para combinar archivos. Así que creé una pequeña y agradable utilidad de software gratuito genérico. Puedes descargarlo y probarlo.
Quiero agradecer a Joe por plantear esa pregunta y quiero agradecerles por pasar. Nos vemos la próxima vez para otro netcast de.
Descargar archivo
Descargue el archivo de muestra aquí: Podcast2107.xlsm
