Você tem uma pasta de trabalho com muitas planilhas. Você deseja enviar cada planilha para uma pessoa diferente. Hoje, uma macro para dividir esses dados.
Assistir vídeo
- Joe + Others está procurando uma maneira de salvar cada planilha em um arquivo diferente
- Útil para Power Query ou após usar Mostrar Páginas de Filtro de Relatório
Transcrição de vídeo
Aprenda Excel com o Podcast, Episódio 2107 - Divida cada planilha em uma nova pasta de trabalho
Ei, bem-vindo de volta. Eu sou e netcast. Sou Bill Jelen.
Eu sabia no fundo da minha cabeça que eu precisava fazer isso há muito tempo, mas dois podcasts recentes realmente trouxeram isso, trouxeram para casa.
Recentemente, no episódio 2106, onde estávamos criando um PDF de All Slicer Combinations. Mais tarde naquele episódio, mostrei um método alternativo em que criamos muitos relatórios dinâmicos, mas ele os coloca todos na mesma pasta de trabalho e recebi um e-mail de Joe da Califórnia dizendo bem, olha, preciso enviar cada planilha para um cliente diferente, e mesma coisa, em meus Seminários Power Excel ao vivo onde eu mostro esse truque, as pessoas dizem, bem, não, não queremos tudo na mesma pasta de trabalho, queremos separadamente e então provavelmente ainda mais importante do que isso, está de volta em episódio 2077, onde falei sobre como o Power Query agora tem a capacidade de combinar todos os arquivos do Excel em uma pasta, certo? E isso é milagroso. Funciona muito bem. Se você tivesse 400 arquivos do Excel, cada um com uma única planilha, ele pegaria todos os dados de todas essas planilhas e os colocaria em uma grade.O que é incrível, mas se tivéssemos quase o mesmo problema. Uma pasta de trabalho com 400 planilhas? Não pode fazer isso, certo. Não pode lidar com isso - ainda. Certo, então agora, 1º de julho de 2017, não dá para lidar com isso. Talvez em seis meses ele possa lidar com isso, mas agora, tem que ser uma folha de trabalho única.
Portanto, precisamos encontrar uma maneira de separar as coisas em arquivos individuais. Ok, então vamos apenas configurar isso. Temos a pasta de trabalho que fiz em 2106, onde temos os dados e, em seguida, a tabela dinâmica original e vamos em Analisar, Opções, Mostrar páginas de filtro de relatório e mostrar as páginas da chave, e isso cria um monte de planilhas diferentes para mim e Quero pegar essas planilhas e criar cada uma em um arquivo separado, mas embora tenhamos isso, há algumas coisas como Planilha 2 e Dados que não quero dividir.
Tudo bem? E, claro, para cada pessoa, essas coisas, essas, essa lista de planilhas, que não queremos dividir, será diferente, mas suponho que quase todo mundo tem algumas planilhas que não não quero dividir.
Certo, aqui está o utilitário que você poderá baixar. O Divisor de planilhas e aqui eu tenho uma seção na coluna B e é realmente a única coisa na coluna B onde você pode listar as planilhas que você não deseja dividir. Pode ser mais de dois. Você pode preencher aqui quantos quiser. Você pode inserir novas linhas e meu jeito barato, eu não queria ter que fazer um loop através delas na Macro, tão longe de sua visão aqui, eu tenho um ponto onde a Macro pode escrever o nome da planilha atual e então VLOOKUP simples. Ele diz, vá procurar esta planilha na qual estamos trabalhando agora, veja se acabou na coluna B e, se for, saberemos que é aquela que não queremos exportar.
Tudo bem e, novamente, apenas para tornar isso o mais genérico possível, eu tenho vários intervalos nomeados aqui, meu caminho, meu prefixo, meu sufixo, meu tipo e minha pasta. Tudo bem, então você descobre para onde quer que essas coisas vão. c: Relatórios . Quero que cada arquivo tenha o nome da folha, mas antes do nome da folha, vou colocar o prefixo de WB, Sufixo de arquivo e nada e então você tem uma escolha aqui: PDF ou XLSX.
Então, vamos começar com o XLSX, falaremos sobre os valores dessa colagem antes de salvar mais tarde. Tudo bem e agora esta é a versão de 1º de julho de 2017, a primeira. Se melhorarmos, vou apenas substituí-lo na página da web e você pode encontrar a página da web lá nas descrições do YouTube. Tudo bem, então aqui está como isso vai funcionar. É um arquivo XLSM. Portanto, você deve se certificar de que as macros são permitidas. alt = "" T, M, S, por segurança você deve estar pelo menos neste nível ou abaixo. Certo, se você está no topo, você precisa mudar, feche a pasta de trabalho, reabra. Quando você abre a pasta de trabalho, vai dizer, ei, você está disposto a aceitar as macros aqui e não é uma macro grande: sessenta e oito linhas de código e muito disso trata apenas de retirar os valores do menu Folha,quais são as variáveis agora.
O importante aqui, porém, é que funcionará no ActiveWorkbook. Então, você irá alternar para a pasta de trabalho que contém os dados e, em seguida, pressione CTRL SHIFT S para executá-la e ela detectará a pasta de trabalho ativa e será aquela que será separada. Ele pega (“MyPath”) e é só porque eu sempre esqueço de colocar aquela barra invertida, se o último caractere não for uma barra invertida, então vou adicionar uma barra invertida e aqui embaixo este é o trabalho real.
Para cada planilha, no original, no WBO ativo. Planilhas, vamos testar para ver se é uma que está ali e na coluna B. Se for, se não estiver, vamos exportar esta planilha e eu amo esta linha de código. WS.copy diz, quando eu pego esta pasta de trabalho, esta planilha, a partir desta grande pasta de trabalho com, você conhece 20 ou 400 planilhas e vamos para WS.copy, que faz uma cópia dela e a move para uma nova pasta de trabalho e sabemos, sabemos que essa nova pasta de trabalho agora se tornará a pasta de trabalho ativa na macro e, claro, há apenas uma planilha nessa pasta de trabalho e essa planilha é a planilha ativa.
Então, certo, aqui posso descobrir o nome da pasta de trabalho. Configure, aplique a esta variável de objeto, pasta de trabalho nova, planilha nova e mais tarde, quando tiver que fechar, posso fazer WBN.close depois de terminar o trabalho. Descobrimos o novo nome do arquivo usando todas as variáveis. Mate aquele arquivo, se já existir e a seguir, se for um arquivo do Excel, a gente salva como, se for um PDF.
E, a propósito, esse código PDF só funciona no Windows, se você estiver em um Mac, desculpe, você terá que ir a outro lugar para descobrir o código Mac equivalente. Eu não tenho um Mac. Eu sei que existe uma maneira de salvar um PDF em um Mac. Eu sei que o código é diferente. Você terá que descobrir isso ou voltar para o Excel real no Windows e então terminar, encerraremos.
Tudo bem, então é apenas uma pequena macro simples como essa, mude para a nossa pasta de trabalho de dados aqui, aquela que tem todas as planilhas. Há 20 planilhas diferentes aqui, mais as duas que não quero fazer e então CTRL SHIFT S assim e veremos piscar enquanto cria cada uma. Aí estamos nós: 21 arquivos criados.
Vamos dar uma olhada no Windows Explorer e aqui está meu SO (C :) Reports, criado para cada planilha, nomeado na pasta de trabalho original, ele criou uma nova versão com WB antecipadamente. Certo, Joe, quando Joe me enviou esta nota, ele disse que enviará esses dados aos clientes e eu meio que entrei em pânico no início porque disse, espere um segundo Joe, vamos ter um problema porque você vai enviar Gary, seus dados, certo? Mas isso é, ah, você conhece um conjunto de dados ao vivo, é uma tabela dinâmica ao vivo. Todas as coisas aqui, você pode conseguir todas as informações para outros clientes assim, certo? Rapaz, você não quer enviar a um cliente A as informações de todos os outros clientes. Isso pode ser um aborrecimento e, na verdade, quando eu reli a nota do programa, ele foi mais esperto do que eu, porque ele disse:Quero criá-los como arquivos PDF. Eu estava tipo, tudo bem, então sim, não precisamos nos preocupar com os arquivos PDF, tudo bem, mas o que eu adicionei aqui, ao Macro foi a capacidade de dizer Colar valores antes de salvar? VERDADE.
Então você define isso igual a TRUE e isso vai invocar este pequeno pedaço de código aqui, onde dizemos, If PasteV Then the UsedRange.Copy e então UsedRange.PasteSpecial (xlPasteValues), UsedRange, em vez de copiar e colar todos os 17 bilhões de células , isso o limitará bem, o UsedRange.
Tudo bem, então vamos voltar, trocar a planilha que contém os dados, CTRL SHIFT S para dividir e então esta nova versão no diretório de relatórios, você verá que se livrou da Tabela Dinâmica e deixou apenas os dados lá. Assim, eles não podem acessar todos os dados.
Alright, we'll try the other feature. We'll try if we switch from Excel to PDF change the prefix to PDFFileOf, whatever we want there. I won't even try the suffix, something. Alright and then switch to the data, CTRL SHIFT S. Alrighty, so we get the same files PDFFileOf the Worksheet name, something of PDF and we should have just nice little PDFs in there, like that.
Alright so there you have it the.com Worksheet Splitter. Hopefully generic enough, for whatever you need. Download it again from the link there in the YouTube comments. To learn more about VBA, check out this book Excel 2016 VBA and Macros by myself and Tracy ?08:50.640. Click that I on the top right hand corner, to read more about the book.
Joe, da Califórnia, e muitos outros pediram uma maneira de salvar cada planilha em um arquivo diferente, seja como um PDF no caso de Joe ou um arquivo Excel, caso você vá usar o Power Query para combinar arquivos. Então eu criei um pequeno utilitário genérico de freeware legal. Você pode fazer o download e experimentar.
Quero agradecer a Joe por ter feito essa pergunta e quero agradecer a sua visita. Nos vemos na próxima vez para outro netcast de.
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2107.xlsm
