Du har en arbeidsbok med mange regneark. Du vil sende hvert regneark til en annen person. I dag en makro for å dele opp dataene.
Se på video
- Joe + Other ser etter en måte å lagre hvert regneark i en annen fil
- Nyttig for Power Query eller etter bruk av Vis rapportfiltersider
Videoutskrift
Lær Excel fra Podcast, episode 2107 - Del hvert regneark i en ny arbeidsbok
Hei velkommen tilbake. Jeg er og netcast. Jeg er Bill Jelen.
Jeg har visst bak i hodet på meg at jeg har trengt å gjøre dette i lang tid, men to nylige podcaster brakte det virkelig, førte det hjem.
Bare nylig i episode 2106, hvor vi opprettet en PDF fra All Slicer Combinations. Sent i den episoden viste jeg en alternativ metode der vi oppretter mange pivotrapporter, men det setter dem alle på den samme arbeidsboken, og jeg fikk en e-post fra Joe i California sier vel, se jeg må sende hvert regneark til en annen kunde, og samme ting, i mine live Power Excel-seminarer der jeg viser det trikset, folk sier, vel nei, vi vil ikke ha det hele i samme arbeidsbok, vi vil ha det hver for seg, og da er det nok enda viktigere enn det, tilbake i episode 2077, hvor jeg snakket om hvordan Power Query nå har muligheten til å kombinere alle Excel-filene i en mappe, ikke sant? Og dette er mirakuløst. Det fungerer bra. Hvis du hadde 400 Excel-filer, hver med et enkelt regneark, vil den hente alle dataene fra alle disse regnearkene og sette dem i ett rutenett.Noe som er kjempebra, men hvis vi hadde nesten det samme problemet. Én arbeidsbok med 400 regneark? Det klarer det ikke, ikke sant. Det kan ikke takle det - ennå. Akkurat, så akkurat nå, 1. juli 2017, kan den ikke takle det. Kanskje om seks måneder kan det takle det, men akkurat nå må det være arbeidsark med ett ark.
Så vi trenger en måte å kunne dele ting ut i individuelle filer. Ok, så la oss bare sette opp dette. Vi har arbeidsboken jeg gjorde i 2106 der vi har dataene og deretter den originale pivottabellen, og vi går inn i Analyse, Alternativer, Vis rapportfiltersider og viser sider med nøkkelen, og det skaper en hel haug med forskjellige regneark for meg og Jeg vil ta disse regnearkene og lage hver og en er en egen fil, men selv om vi har det, er det noen ting som Sheet2 og Data som jeg ikke vil dele.
Ok? Og selvfølgelig vil hver ting, ting, de, listen over regneark, som vi ikke vil dele, være annerledes, men jeg kommer til å gjette at nesten alle har noen regneark som de ikke har ' t vil splitte.
Greit, så her er verktøyet du skal kunne laste ned. Worksheets Splitter og her borte har jeg en seksjon i kolonne B, og det er egentlig det eneste i kolonne B hvor du kan liste de regnearkene du ikke vil dele. Det kan være mer enn to. Du kan fylle ut så mange her du vil. Du kan sette inn nye rader og min billige måte. Jeg ønsket ikke å gå gjennom disse i makroen, så langt utenfor synet ditt her, jeg har et sted hvor makroen kan skrive det nåværende regnearknavnet og deretter en enkel liten VLOOKUP. Det står, gå og se etter dette regnearket vi jobber med akkurat nå, se om det er over i kolonne B, og hvis det er, vet vi at det er et vi ikke vil eksportere.
Greit og så igjen bare for å gjøre dette så generisk som mulig, jeg har flere navngitte områder her, stien min, prefikset, suffikset, typen og limen. Greit, så du finner ut hvor du vil at disse tingene skal gå. c: Rapporter . Jeg vil at hver fil skal ha arknavnet, men før arknavnet skal jeg legge prefikset til WB, File Suffix og ingenting, og så har du et valg her: PDF eller XLSX.
Så vi skal begynne med XLSX, vi snakker om denne limverdiene før vi lagrer senere. Greit og akkurat nå er dette versjonen 1. juli 2017, den første. Hvis vi forbedrer dette, vil jeg bare erstatte det på websiden, og du kan finne websiden der nede i YouTube-beskrivelsene. Greit så her er hvordan dette kommer til å fungere. Det er en XLSM-fil. Så du må sørge for at makroer er tillatt. alt = "" T, M, S, for sikkerhet må du være minst på dette nivået eller under. Rett hvis du er øverst, må du endre, den lukker arbeidsboken, åpnes på nytt. Når du åpner arbeidsboken, vil det si, hei er du villig til å akseptere makroene her, og det er ikke en stor makro i det hele tatt: sekstiåtte linjer med kode og mye av det handler bare om å få verdiene fra menyen Ark,hva er variablene nå.
Det viktige her er imidlertid at det kommer til å fungere på ActiveWorkbook. Så du kommer til å bytte til arbeidsboken som har dataene, og trykk deretter på CTRL SHIFT S for å kjøre den, og den vil oppdage ActiveWorkbook, og det kommer til å være den den deles ut. Den griper (“MyPath”) og det er bare fordi jeg alltid glemmer å sette den omvendte skråstrek, hvis den siste karakteren ikke er en tilbakeslag, så skal jeg legge til en tilbakeslag og så her nede er dette selve arbeidet.
For hvert regneark, i originalen, i det aktive WBO.Worksheets, skal vi teste for å se om det er et der borte og kolonne B. Hvis det er, hvis det ikke er det, skal vi eksportere dette arket og jeg elsker denne linjen med kode. WS.copy sier, når jeg tar denne arbeidsboken, dette regnearket, fra denne store arbeidsboken med, vet du 20 eller 400 regneark, og vi skal til WS.copy, som lager en kopi av den og flytter den til en ny arbeids arbeidsbok og vi vet, vi vet at den nye arbeidsboken nå blir den aktive arbeidsboken i makroen, og selvfølgelig er det bare ett ark i arbeidsboken, og det arket er det aktive arket.
Så riktig, her kan jeg finne ut navnet på arbeidsboken. Sett det, bruk på denne objektvariabelen, arbeidsbok ny, regneark ny og senere, når jeg må lukke, kan jeg gjøre WBN.close etter at jeg har gjort arbeidet. Vi finner ut det nye filnavnet ved hjelp av alle variablene. Drep den filen, hvis den allerede eksisterer, og hvis det er en Excel-fil, lagrer vi som, hvis det er en PDF.
Og forresten, denne PDF-koden fungerer bare i Windows, hvis du er på en Mac, beklager, må du gå et annet sted for å finne ut den tilsvarende Mac-koden. Jeg har ikke en Mac. Jeg vet at det er en måte å lagre en PDF på en Mac. Jeg vet at koden er annerledes. Du må finne ut av den eller komme tilbake til den virkelige Excel i Windows, og så er vi ferdige, vi lukker.
Greit, så det er bare en enkel liten makro som den, bytt til vår Data Workbook her, den som har alle regnearkene. Det er 20 forskjellige regneark her, pluss de to jeg ikke vil gjøre, og så CTRL SKIFT S slik, så ser vi at det blinker når det skaper hver enkelt. Der er vi: 21 filer opprettet.
La oss ta en titt i Windows Utforsker, og her er operativsystemet mitt (C :), det ble opprettet for hvert regneark, oppkalt i den opprinnelige arbeidsboken, det opprettet en ny versjon med WB foran. Greit nå, Joe, da Joe sendte meg dette notatet, sa han at han kommer til å sende disse dataene til kundene, og jeg fikk litt panikk i begynnelsen fordi jeg sa, vent et øyeblikk Joe, vi kommer til å ha et problem fordi du er kommer til å sende Gary, hans data, ikke sant? Men dette er, ah, du kjenner et live, et live datasett, det er et live pivottabell. Alt av ting her inne, kan du kanskje få all informasjon for andre kunder som det, ikke sant? Gutt, du vil ikke sende en kunde A informasjonen til alle de andre kundene. Det kan være et problem og når jeg leser om på nytt, var han smartere enn meg, fordi han sa:Jeg vil lage dem som PDF-filer. Jeg var som, ok, ja da, vi trenger ikke å bekymre oss for som PDF-filer, det er greit, men det jeg la til her, til makroen, var muligheten til å si Lim inn verdier før du lagrer? EKTE.
Så du setter det samme som SANT, og det kommer til å påkalle denne lille biten kode her, hvor vi sier: Hvis PasteV så UsedRange.Copy og deretter UsedRange.PasteSpecial (xlPasteValues), UsedRange, i stedet for å kopiere og lime inn alle 17 milliarder celler , det vil begrense det til godt, UsedRange.
OK, så la oss bytte tilbake, bytte det regnearket som har dataene, CTRL SHIFT S for split og deretter denne nye versjonen i rapportkatalogen, du vil se at den har blitt kvitt pivottabellen og bare lagt igjen dataene der. Så på den måten kan de ikke komme til all data.
Greit, vi prøver den andre funksjonen. Vi prøver om vi bytter fra Excel til PDF, og endrer prefikset til PDFFileOf, uansett hva vi vil der. Jeg vil ikke engang prøve suffikset, noe. Ok, og bytt deretter til dataene, CTRL SHIFT S. Greit, så vi får de samme filene PDFFileOf regnearknavnet, noe av PDF, og vi burde ha bare fine små PDF-filer der inne, sånn.
Greit, så der har du det ..com regneark splitter. Forhåpentligvis generisk nok, for hva du trenger. Last den ned igjen fra lenken der i YouTube-kommentarene. For å lære mer om VBA, sjekk ut denne boken Excel 2016 VBA og makroer av meg selv og Tracy? 08: 50.640. Klikk på jeg øverst til høyre for å lese mer om boka.
Joe, fra California, pluss mange andre har bedt om en måte å lagre hvert regneark på i en annen fil, enten som en PDF i Joes tilfelle eller som en Excel-fil i tilfelle du skal bruke Power Query til å kombinere filer. Så jeg opprettet et fint lite Generic Freeware Utility der ute. Du kan laste ned og gi den en sjanse.
Jeg vil takke Joe for å stille det spørsmålet og vil takke deg for at du var innom. Vi sees neste gang for en ny netcast fra.
Last ned fil
Last ned eksempelfilen her: Podcast2107.xlsm
