Du har en projektmappe med mange regneark. Du vil sende hvert regneark til en anden person. I dag er en makro til at opdele disse data ud.
Se video
- Joe + Andre leder efter en måde at gemme hvert regneark i en anden fil
- Nyttigt til Power Query eller efter brug af Vis rapportfiltersider
Videoudskrift
Lær Excel fra Podcast, afsnit 2107 - Opdel hvert regneark til en ny projektmappe
Hej velkommen tilbage. Jeg er og netcast. Jeg er Bill Jelen.
Jeg har vidst bagest på mit hoved, at jeg har haft brug for det i lang tid, men to nylige podcasts bragte det virkelig, bragte det hjem.
For nylig i afsnit 2106, hvor vi oprettede en PDF fra All Slicer Combinations. Sent i denne episode viste jeg en alternativ metode, hvor vi opretter mange pivotrapporter, men det sætter dem alle på den samme projektmappe, og jeg fik en e-mail fra Joe i Californien, siger godt, se, jeg skal sende hvert regneark til en anden kunde, og samme ting, i mine live Power Excel-seminarer, hvor jeg viser det trick, siger folk, ja nej, vi vil ikke have det hele i den samme projektmappe, vi vil have det separat og så sandsynligvis endnu vigtigere end det, er tilbage i afsnit 2077, hvor jeg talte om, hvordan Power Query nu har evnen til at kombinere alle Excel-filerne i en mappe, ikke? Og dette er mirakuløst. Det fungerer godt. Hvis du havde 400 Excel-filer, hver med et enkelt regneark, vil det hente alle disse data fra alle disse regneark og sætte det i et gitter.Hvilket er fantastisk, men hvis vi havde næsten det samme problem. En arbejdsbog med 400 regneark? Det kan ikke gøre det, ikke. Det kan ikke håndtere det - endnu. Lige så lige nu, 1. juli 2017, kan det ikke håndtere det. Måske om seks måneder kan det håndtere det, men lige nu skal det være arbejdsark med enkelt ark.
Så vi har brug for en måde at være i stand til at opdele tingene i individuelle filer. Okay, så lad os bare sætte dette op. Vi har den projektmappe, jeg lavede i 2106, hvor vi har dataene og derefter den originale pivottabel, og vi går ind i Analyse, Indstillinger, Vis rapportfiltersider og viser sider med nøglen, og det skaber en hel række forskellige regneark til mig og Jeg vil tage disse regneark og oprette hver enkelt er en separat fil, men selvom vi har det, er der nogle ting som Sheet2 og Data, som jeg ikke vil dele.
I orden? Og selvfølgelig for hver enkelt person, vil de ting, dem, den liste over regneark, som vi ikke vil opdele, være anderledes, men jeg vil gætte, at næsten alle har nogle regneark, som de ikke har ' t ønsker at splitte.
Okay, så her er det hjælpeprogram, du kan downloade. Worksheets Splitter og herovre har jeg et afsnit i kolonne B, og det er virkelig den eneste ting i kolonne B, hvor du kan liste de regneark, som du ikke vil dele. Det kan være mere end to. Du kan udfylde så mange her, som du vil. Du kan indsætte nye rækker og min billige måde, jeg ønskede ikke at skulle løbe igennem disse i makroen, så langt uden for dit syn herude, jeg har et sted, hvor makroen kan skrive det aktuelle regnearksnavn og derefter et enkel lille VLOOKUP. Det siger, gå efter dette regneark, som vi arbejder på lige nu, se om det er slut i kolonne B, og hvis det er, ved vi, at det er et, som vi ikke vil eksportere.
Okay og så igen bare for at gøre dette så generisk som muligt, har jeg flere navngivne områder her, min sti, mit præfiks, mit suffiks, min type og min pasta. Okay, så du finder ud af, hvor du vil have disse ting hen. c: Rapporter . Jeg vil have, at hver fil skal have arknavnet, men før arknavnet skal jeg sætte præfikset for WB, File Suffix og intet, og så har du et valg her: PDF eller XLSX.
Så vi starter med XLSX, vi taler om denne indsætningsværdier, før vi gemmer senere. Okay og lige nu er dette version 1. juli 2017, den første. Hvis vi forbedrer dette, skal jeg bare erstatte det på websiden, og du kan finde websiden dernede i YouTube-beskrivelserne. Okay så her er hvordan det skal fungere. Det er en XLSM-fil. Så du skal sørge for, at makroer er tilladt. alt = "" T, M, S, for sikkerhed skal du være mindst på dette niveau eller derunder. Lige hvis du er øverst, skal du ændre det, luk arbejdsbogen, åbn den igen. Når du åbner projektmappen, vil det sige, hej er du villig til at acceptere makroerne her, og det er slet ikke en stor makro: 64 linjer med kode og meget af det handler bare om at få værdierne fra menuen Ark,hvad er variablerne nu.
Det vigtige her er dog, at det kommer til at arbejde på ActiveWorkbook. Så du kommer til at skifte til den projektmappe, der har dataene, og tryk derefter på CTRL SHIFT S for at køre den, og den vil registrere ActiveWorkbook, og det bliver den, den deler op. Det griber (“MyPath”), og det er bare fordi jeg altid glemmer at sætte den omvendte skråstreg, hvis den sidste karakter ikke er en tilbageslag, så vil jeg tilføje en tilbageslag og så hernede er dette det egentlige arbejde.
For hvert regneark, i originalen, i det aktive WBO.Worksheets, vil vi teste for at se, om det er et derovre og kolonne B. Hvis det er, hvis det ikke er det, eksporterer vi dette ark, og jeg elsker denne kodelinje. WS.copy siger, når jeg tager denne projektmappe, dette regneark, fra denne store projektmappe med, kender du 20 eller 400 regneark, og vi går til WS.copy, som laver en kopi af den og flytter den til en ny arbejdsmappe og vi ved, vi ved, at den nye projektmappe nu bliver den aktive projektmappe i makroen, og der er selvfølgelig kun et ark i den projektmappe, og det ark er det aktive ark.
Så lige her kan jeg finde ud af navnet på projektmappen. Indstil det, anvend på denne objektvariabel, projektmappe ny, regneark ny og derefter senere, når jeg skal lukke, kan jeg lave WBN.close efter at jeg har udført arbejdet. Vi finder ud af det nye filnavn ved hjælp af alle variablerne. Dræb den fil, hvis den allerede findes, og hvis det er en Excel-fil, gemmer vi som, hvis det er en PDF.
Og forresten fungerer denne PDF-kode kun i Windows, hvis du er på en Mac, undskyld, skal du gå et andet sted for at finde ud af den tilsvarende Mac-kode. Jeg har ikke en Mac. Jeg ved, at der er en måde at gemme en PDF på en Mac. Jeg ved, at koden er anderledes. Du bliver nødt til at finde ud af den ene eller komme tilbage til den rigtige Excel i Windows, og så er vi færdige, vi lukker.
Okay, så det er bare en simpel lille makro sådan, skift til vores data-projektmappe her, den der har alle regnearkene. Der er 20 forskellige regneark her plus de to, jeg ikke vil lave, og derefter CTRL SHIFT S som dette, og vi ser det blinke, når det opretter hver enkelt. Der er vi: 21 filer oprettet.
Lad os kigge i Windows Stifinder, og her er mit OS (C :) -rapporter, det blev oprettet for hvert regneark, navngivet i den originale projektmappe, det oprettede en ny version med WB foran. Okay, Joe, da Joe sendte denne note til mig, sagde han, at han vil sende disse data til kunder, og jeg blev lidt panik i starten, fordi jeg sagde, vent et øjeblik Joe, vi får et problem, fordi du vil sende Gary, hans data, ikke? Men dette er, ah, du kender et live, et live datasæt, det er et live pivottabel. Alt det her, kan du muligvis få al information til andre kunder sådan, ikke? Dreng, du vil ikke sende en kunde A oplysningerne til alle de andre kunder. Det kunne være besvær, og faktisk, når jeg læste om showet, var han klogere end mig, fordi han sagde:Jeg vil oprette dem som PDF-filer. Jeg var som, okay, ja da, vi behøver ikke bekymre os om som PDF-filer, det er fint, men det jeg tilføjede herinde til makroen var evnen til at sige Indsæt værdier, før du gemmer? SAND.
Så du indstiller det lig med SAND, og det vil påkalde denne lille smule kode her, hvor vi siger: Hvis PasteV Så UsedRange.Copy og derefter UsedRange.PasteSpecial (xlPasteValues), UsedRange, snarere end at kopiere og indsætte alle 17 milliarder celler , det begrænser det til godt, UsedRange.
Okay, så lad os skifte tilbage, skifte det regneark, der har dataene, CTRL SHIFT S til split og derefter denne nye version i rapportmappen, du vil se, at det er sluppet af pivottabellen og kun har efterladt dataene der. Så på den måde kan de ikke komme til alle data.
Okay, vi prøver den anden funktion. Vi prøver, hvis vi skifter fra Excel til PDF, skifter præfikset til PDFFileOf, uanset hvad vi vil der. Jeg prøver ikke engang suffikset, noget. Okay, og skift derefter til dataene, CTRL SHIFT S. Alrighty, så vi får de samme filer PDFFileOf regnearknavnet noget af PDF, og vi skal have bare fine små PDF-filer derinde, sådan.
Okay så der har du det. Com Worksheet Splitter. Forhåbentlig generisk nok til hvad du har brug for. Download det igen fra linket der i YouTube-kommentarerne. For at lære mere om VBA, tjek denne bog Excel 2016 VBA og makroer af mig selv og Tracy? 08: 50.640. Klik på at jeg øverst til højre for at læse mere om bogen.
Joe fra Californien plus mange andre har bedt om en måde at gemme hvert regneark på i en anden fil, enten som en PDF i Joes tilfælde eller som en Excel-fil, hvis du vil bruge Power Query til at kombinere filer. Så jeg oprettede et dejligt lille Generic Freeware Utility derude. Du kan downloade og give det et skud.
Jeg vil takke Joe for at stille dette spørgsmål og vil takke dig for at komme forbi. Vi ses næste gang til endnu en netcast fra.
Download fil
Download eksempelfilen her: Podcast2107.xlsm
