Hai una cartella di lavoro con molti fogli di lavoro. Vuoi inviare ogni foglio di lavoro a una persona diversa. Oggi, una macro per suddividere quei dati.
Guarda un video
- Joe + Altri sta cercando un modo per salvare ogni foglio di lavoro in un file diverso
- Utile per Power Query o dopo aver utilizzato Mostra pagine filtro report
Trascrizione del video
Impara Excel da Podcast, episodio 2107 - Dividi ogni foglio di lavoro in una nuova cartella di lavoro
Ehi bentornato. Sono e netcast. Sono Bill Jelen.
Sapevo nella parte posteriore della mia testa che avevo bisogno di farlo per molto tempo, ma due podcast recenti lo hanno davvero portato a casa.
Proprio di recente nell'episodio 2106, dove stavamo creando un PDF da Tutte le combinazioni di filtri dei dati. Alla fine di quell'episodio ho mostrato un metodo alternativo in cui creiamo molti rapporti pivot, ma li inserisce tutti nella stessa cartella di lavoro e ho ricevuto un'e-mail da Joe in California che dice bene, guarda, devo inviare ogni foglio di lavoro a un cliente diverso e stessa cosa, nei miei seminari Power Excel dal vivo in cui mostro quel trucco, le persone dicono, beh no, non lo vogliamo tutto nella stessa cartella di lavoro, lo vogliamo separatamente e quindi probabilmente anche più importante di quello, è tornato episodio 2077, in cui ho parlato di come Power Query ora ha la capacità di combinare tutti i file Excel in una cartella, giusto? E questo è miracoloso. Funziona benissimo. Se hai 400 file Excel, ciascuno con un singolo foglio di lavoro, raccoglierà tutti i dati da tutti quei fogli di lavoro e li metterà in una griglia.Il che è fantastico, ma se avessimo quasi lo stesso problema. Una cartella di lavoro con 400 fogli di lavoro? Non può farlo, giusto. Non può affrontarlo - ancora. Bene, quindi in questo momento, il 1 ° luglio 2017, non può affrontarlo. Forse in sei mesi può farcela, ma in questo momento devono essere cartelle di lavoro a foglio singolo.
Quindi abbiamo bisogno di un modo per essere in grado di suddividere le cose in singoli file. Ok, quindi impostiamo questo. Abbiamo la cartella di lavoro che ho creato nel 2106 in cui abbiamo i dati e poi la tabella pivot originale e andiamo in Analizza, Opzioni, Mostra pagine filtro report e mostra le pagine della chiave, e crea un intero gruppo di fogli di lavoro diversi per me e Voglio prendere quei fogli di lavoro e creare ognuno di essi è un file separato, ma anche se ce l'abbiamo, ci sono alcune cose come Sheet2 e Data che non voglio dividere.
Tutto a posto? E ovviamente per ogni singola persona, quella roba, quella lista di fogli di lavoro, che non vogliamo dividere, sarà diversa, ma immagino che quasi tutti abbiano dei fogli di lavoro che non hanno. Voglio dividere.
Va bene, quindi ecco l'utilità che potrai scaricare. The Worksheets Splitter e qui ho una sezione nella colonna B ed è davvero l'unica cosa nella colonna B in cui puoi elencare quei fogli di lavoro che non vuoi dividere. Possono essere più di due. Puoi inserirne quanti ne vuoi qui. Puoi inserire nuove righe e il mio modo economico, non volevo doverle scorrere nella Macro, finora fuori dalla tua vista qui, ho un punto in cui la Macro può scrivere il nome del foglio di lavoro corrente e quindi un semplice piccolo CERCA. Dice, vai a cercare questo foglio di lavoro su cui stiamo lavorando in questo momento, vedi se è finito nella colonna B e se lo è, sapremo che è quello che non vogliamo esportare.
Va bene e poi di nuovo solo per renderlo il più generico possibile, ho diversi intervalli di nomi qui, il mio percorso, il mio prefisso, il mio suffisso, il mio tipo e il mio incolla. Va bene, quindi capisci dove vuoi che questa roba vada. c: Reports . Voglio che ogni file abbia il nome del foglio, ma prima del nome del foglio, metto il prefisso di WB, Suffisso file e niente e poi hai una scelta qui: PDF o XLSX.
Quindi inizieremo con XLSX, parleremo di questi valori di incolla prima di salvare in seguito. Va bene e in questo momento questa è la versione del 1 ° luglio 2017, la prima. Se miglioriamo questo, lo sostituirò semplicemente sulla pagina web e potrai trovare la pagina web laggiù nelle descrizioni di YouTube. Va bene, ecco come funzionerà. È un file XLSM. Quindi devi assicurarti che le macro siano consentite. alt = "" T, M, S, per sicurezza devi essere almeno a questo livello o inferiore. Esatto, se sei in alto, devi cambiare, chiudere la cartella di lavoro, riaprire. Quando apri la cartella di lavoro, dirà, ehi, sei disposto ad accettare le macro qui e non è affatto una grande macro: sessantotto righe di codice e molte di queste riguardano solo l'estrazione dei valori dal menu Foglio,quali sono le variabili adesso.
La cosa importante qui però è che funzionerà su ActiveWorkbook. Quindi passerai alla cartella di lavoro che contiene i dati e quindi premi CTRL MAIUSC S per eseguirlo e rileverà l'ActiveWorkbook e sarà quello che divide. Afferra ("MyPath") ed è solo perché mi dimentico sempre di mettere quel backslash, se l'ultimo carattere non è un backslash, allora aggiungerò un backslash e poi qui questo è il lavoro effettivo.
Per ogni foglio di lavoro, nell'originale, nel WBO attivo. Fogli di lavoro, testeremo per vedere se è uno lì e la colonna B. Se lo è, se non lo è, esportiamo questo foglio e io amo questa riga di codice. WS.copy dice, quando prendo questa cartella di lavoro, questo foglio di lavoro, da questa grande cartella di lavoro con, conosci 20 o 400 fogli di lavoro e andremo a WS.copy, che ne fa una copia e la sposta in una nuova cartella di lavoro di lavoro e sappiamo, sappiamo che quella nuova cartella di lavoro diventerà ora la cartella di lavoro attiva nella macro e ovviamente c'è solo un foglio in quella cartella di lavoro e quel foglio è il foglio attivo.
Quindi bene, qui posso capire il nome della cartella di lavoro. Impostalo, Applica a questa variabile oggetto, Nuova cartella di lavoro, Nuovo foglio di lavoro e poi in seguito, quando devo chiudere, posso fare WBN.close dopo aver terminato il lavoro. Calcoliamo il nuovo nome del file utilizzando tutte le variabili. Uccidi quel file, se esiste già e poi, se è un file Excel, facciamo un salvataggio come, se è un PDF.
E tra l'altro questo codice PDF funziona solo su Windows, se sei su un Mac, scusa, dovrai andare da qualche altra parte per capire il codice Mac equivalente. Non ho un Mac. So che c'è un modo per salvare un PDF su un Mac. So che il codice è diverso. Dovrai capirlo o tornare al vero Excel in Windows e poi abbiamo finito, chiudiamo.
Va bene, quindi è solo una semplice piccola Macro come quella, passa alla nostra cartella di lavoro dei dati qui, quella che ha tutti i fogli di lavoro. Ci sono 20 diversi fogli di lavoro qui, più i due che non voglio fare e poi CTRL MAIUSC S in questo modo e lo vedremo lampeggiare mentre crea ciascuno di essi. Ci siamo: 21 file creati.
Diamo un'occhiata in Windows Explorer ed ecco il mio OS (C :) Report, creato per ogni foglio di lavoro, denominato nella cartella di lavoro originale, ha creato una nuova versione con WB in primo piano. Va bene, Joe, quando Joe mi ha inviato questa nota, ha detto che invierà questi dati ai clienti e all'inizio sono stato preso dal panico perché ho detto, aspetta un secondo Joe, avremo un problema perché tu manderò Gary, i suoi dati, giusto? Ma questo è, ah, conosci un live, un set di dati live, è una tabella pivot live. Tutte le cose qui dentro, potresti essere in grado di ottenere tutte le informazioni per altri clienti in questo modo, giusto? Ragazzo, non vuoi inviare a un cliente A le informazioni per tutti gli altri clienti. Potrebbe essere una seccatura e in realtà, quando ho riletto la nota dello spettacolo, è stato più intelligente di me, perché ha detto:Voglio crearli come file PDF. Ero tipo, va bene, beh, allora sì, non dobbiamo preoccuparci di come file PDF, va bene, ma quello che ho aggiunto qui, alla Macro era la possibilità di dire Incolla i valori prima di salvare? VERO.
Quindi lo imposti uguale a TRUE e questo richiamerà questo minuscolo bit di codice qui, dove diciamo, If PasteV Then UsedRange.Copy e poi UsedRange.PasteSpecial (xlPasteValues), UsedRange, piuttosto che copiare e incollare tutti i 17 miliardi di celle , lo limiterà a bene, UsedRange.
Va bene, quindi torniamo indietro, cambiamo il foglio di lavoro che contiene i dati, CTRL MAIUSC S per dividere e quindi questa nuova versione nella directory dei rapporti, vedrai che si è sbarazzato della tabella pivot e ha lasciato lì solo i dati. In questo modo non possono accedere a tutti i dati.
Va bene, proveremo l'altra funzionalità. Proveremo se passiamo da Excel a PDF a cambiare il prefisso in PDFFileOf, qualunque cosa vogliamo lì. Non proverò nemmeno il suffisso, qualcosa. Va bene, quindi passa ai dati, CTRL MAIUSC S. Va bene, quindi otteniamo gli stessi file PDFFileOf del nome del foglio di lavoro, qualcosa di PDF e dovremmo avere solo dei bei PDF lì dentro, così.
Va bene, ecco qua il.com Worksheet Splitter. Si spera abbastanza generico, per tutto ciò di cui hai bisogno. Scaricalo di nuovo dal link presente nei commenti di YouTube. Per saperne di più su VBA, dai un'occhiata a questo libro Excel 2016 VBA e macro da me e da Tracy? 08: 50.640. Fare clic su I nell'angolo in alto a destra per saperne di più sul libro.
Joe, dalla California, e molti altri hanno chiesto un modo per salvare ogni foglio di lavoro in un file diverso, come PDF nel caso di Joe o come file Excel nel caso in cui utilizzerai Power Query per combinare i file. Quindi ho creato una piccola e graziosa utility freeware generica là fuori. Puoi scaricarlo e provarlo.
Voglio ringraziare Joe per aver posto questa domanda e voglio ringraziarti per essere passato. Ci vediamo la prossima volta per un altro netcast da.
Download file
Scarica il file di esempio qui: Podcast2107.xlsm
