Du har en arbetsbok med många kalkylblad. Du vill skicka varje kalkylblad till en annan person. Idag, ett makro för att dela ut data.
Kolla på video
- Joe + Other letar efter ett sätt att spara varje kalkylblad i en annan fil
- Användbar för Power Query eller efter användning av Visa rapportfiltersidor
Videoutskrift
Lär dig Excel från Podcast, avsnitt 2107 - Dela upp varje kalkylblad till en ny arbetsbok
Hej välkommen tillbaka. Jag är och netcast. Jag heter Bill Jelen.
Jag har vetat på baksidan av mitt huvud att jag har behövt göra det länge, men två senaste podcasts tog verkligen med det, tog med det hem.
Nyligen i avsnitt 2106, där vi skapade en PDF från All Slicer Combinations. Sent i det avsnittet visade jag en alternativ metod där vi skapar många pivotrapporter, men det sätter dem alla på samma arbetsbok och jag fick ett e-postmeddelande från Joe i Kalifornien säger bra, se jag måste skicka varje arbetsblad till en annan kund samma sak, i mitt live Power Excel-seminarier där jag visar det tricket, säger folk, ja nej, vi vill inte ha allt i samma arbetsbok, vi vill ha det separat och då är det förmodligen ännu viktigare än det, tillbaka i avsnitt 2077, där jag pratade om hur Power Query nu har förmågan att kombinera alla Excel-filer i en mapp, eller hur? Och detta är mirakulöst. Det fungerar bra. Om du hade 400 Excel-filer, var och en med ett enda kalkylblad, kommer det att fånga all den informationen från alla dessa kalkylblad och lägga den i ett rutnät.Vilket är fantastiskt, men om vi hade nästan samma problem. En arbetsbok med 400 arbetsblad? Det kan inte, eller hur. Det kan inte hantera det - ännu. Precis, så just nu, 1 juli 2017, kan det inte hantera det. Kanske om sex månader kan det hantera det, men just nu måste det vara arbetsböcker på en sida.
Så vi behöver ett sätt att kunna dela upp saker i enskilda filer. Okej, så låt oss bara ställa in det här. Vi har den arbetsbok jag gjorde 2106 där vi har data och sedan den ursprungliga pivottabellen och vi går in i Analysera, Alternativ, Visa rapportfiltersidor och visa sidor med nyckeln, och det skapar en hel massa olika kalkylblad för mig och Jag vill ta dessa kalkylblad och skapa var och en är en separat fil, men även om vi har det finns det några saker som Sheet2 och Data som jag inte vill dela.
OK? Och för varje enskild person kommer naturligtvis de sakerna, de, listan över kalkylblad, som vi inte vill dela, att vara annorlunda, men jag kommer att gissa att nästan alla har några kalkylblad som de inte har ' t vill dela.
Okej, så här är verktyget som du kommer att kunna ladda ner. Kalkylbladssplittern och här har jag ett avsnitt i kolumn B och det är verkligen det enda i kolumn B där du kan lista de kalkylblad som du inte vill dela. Det kan vara mer än två. Du kan fylla i så många här som du vill. Du kan infoga nya rader och mitt billiga sätt, jag ville inte behöva gå igenom dessa i makroen, så långt utanför din syn här, jag har en plats där makroen kan skriva det aktuella kalkylbladets namn och sedan en enkel liten VLOOKUP. Det står, leta efter det här arbetsbladet som vi arbetar med just nu, se om det är över i kolumn B och och om det är så vet vi att det är ett som vi inte vill exportera.
Okej och sedan bara för att göra detta så generiskt som möjligt, jag har flera namngivna områden här, min sökväg, mitt prefix, mitt suffix, min typ och min klistra in. Okej, så du räknar ut var du vill att de här sakerna ska gå. c: Rapporter . Jag vill att varje fil ska ha arknamnet, men innan arknamnet ska jag lägga prefixet WB, File Suffix och ingenting och då har du ett val här: PDF eller XLSX.
Så vi ska börja med XLSX, vi pratar om de här klistra värdena innan vi sparar senare. Okej och just nu är detta version 1 juli 2017, den första. Om vi förbättrar det kommer jag bara att ersätta det på webbsidan så hittar du webbsidan där nere i YouTube-beskrivningarna. Okej så här är hur det här kommer att fungera. Det är en XLSM-fil. Så du måste se till att makron är tillåtna. alt = "" T, M, S, för säkerhet måste du vara åtminstone på denna nivå eller lägre. Om du är högst upp måste du ändra, stäng arbetsboken, öppna den igen. När du öppnar arbetsboken kommer det att säga, hej är du villig att acceptera makron här och det är inte en stor makro alls: sextioåtta rader kod och mycket av det handlar bara om att få värdena från menyn Ark,vad är variablerna nu.
Det viktiga här är dock att det kommer att fungera på ActiveWorkbook. Så du kommer att byta till arbetsboken som har data och sedan trycka på CTRL SHIFT S för att köra den och den kommer att upptäcka ActiveWorkbook och det kommer att bli den som den delas ut. Det tar tag (“MyPath”) och det är bara för att jag alltid glömmer att sätta den bakåtvända snedstrecket, om den sista karaktären inte är en bakåtvänd snedstreck, så kommer jag att lägga till en bakåtvänd snedstreck och sedan här är detta själva arbetet.
För varje kalkylblad, i originalet, i det aktiva WBO.Worksheets, ska vi testa för att se om det är ett där borta och kolumn B. Om det är, om det inte är det, ska vi exportera det här arket och jag älskar denna kod. WS.copy säger, när jag tar den här arbetsboken, det här arbetsbladet, från den här stora arbetsboken med, vet du 20 eller 400 arbetsblad och vi ska till WS.copy, som gör en kopia av den och flyttar den till en ny arbetsarbetsbok och vi vet, vi vet att den nya arbetsboken nu kommer att bli den aktiva arbetsboken i makroen och naturligtvis finns det bara ett ark i den arbetsboken och det arket är det aktiva arket.
Så rätt, här kan jag ta reda på namnet på arbetsboken. Ställ in den, applicera på den här objektvariabeln, arbetsbok ny, kalkylblad ny och sedan senare, när jag måste stänga, kan jag göra WBN.close efter att jag har gjort jobbet. Vi räknar ut det nya filnamnet med alla variabler. Döda den filen, om den redan finns och sedan, om det är en Excel-fil, sparar vi som, om det är en PDF.
Och förresten fungerar den här PDF-koden bara i Windows, om du är på en Mac, tyvärr, måste du gå någon annanstans för att räkna ut motsvarande Mac-kod. Jag har ingen Mac. Jag vet att det finns ett sätt att spara en PDF på en Mac. Jag vet att koden är annorlunda. Du måste räkna ut den där eller komma tillbaka till den verkliga Excel i Windows och då är vi klara, vi stänger.
Okej, så det är bara en enkel liten makro så, byt över till vår dataarkbok här, den som har alla kalkylblad. Det finns 20 olika kalkylblad här, plus de två som jag inte vill göra och sedan CTRL SHIFT S så här och vi kommer att se det blinka när det skapar var och en. Där är vi: 21 filer skapade.
Låt oss ta en titt i Windows Explorer och här är mitt operativsystem (C :) -rapporter, det skapades för varje kalkylblad, namnet i den ursprungliga arbetsboken, det skapade en ny version med WB framför. Okej nu, Joe, när Joe skickade mig den här anteckningen sa han att han kommer att skicka dessa uppgifter till kunder och jag blev typ av panik först eftersom jag sa, vänta en sekund Joe, vi kommer att ha ett problem eftersom du är kommer att skicka Gary, hans data, eller hur? Men det här är, ah, du känner till en live, en live datamängd, det är en live Pivot Table. Allt det här, kanske du kan få all information för andra kunder som det, eller hur? Pojke, du vill inte skicka en kund A informationen till alla andra kunder. Det kan vara besvärligt och faktiskt, när jag läser igenom showen, var han smartare än jag, för han sa:Jag vill skapa dem som PDF-filer. Jag var som, okej, ja då, vi behöver inte oroa oss som PDF-filer, det är bra, men det jag lade till här, till makroen var möjligheten att säga klistra in värden innan du sparar? SANN.
Så du ställer in det lika med SANT och det kommer att anropa den här lilla biten av kod här, där vi säger, Om PasteV Då UsedRange.Copy och sedan UsedRange.PasteSpecial (xlPasteValues), UsedRange, snarare än att kopiera och klistra in alla 17 miljarder celler , det kommer att begränsa det till bra, UsedRange.
Okej, så låt oss byta tillbaka, byta de kalkylblad som har data, CTRL SKIFT S för delning och sedan den här nya versionen i rapportkatalogen, du kommer att se att den har blivit av med pivottabellen och lämnat bara data där. Så på så sätt kan de inte komma till all data.
Okej, vi testar den andra funktionen. Vi försöker om vi byter från Excel till PDF, ändrar prefixet till PDFFileOf, vad vi än vill ha där. Jag kommer inte ens prova suffixet, något. Okej och byt sedan till data, CTRL SHIFT S. Okej, så vi får samma filer PDFFileOf kalkylbladets namn, något av PDF och vi borde ha bara fina små PDF-filer där, som det.
Okej så där har du det. Kalkylbladet Splitter. Förhoppningsvis generiskt nog, för vad du än behöver. Ladda ner den igen från länken där i YouTube-kommentarerna. För att lära dig mer om VBA, kolla in den här boken Excel 2016 VBA och makron av mig själv och Tracy? 08: 50.640. Klicka på jag i det övre högra hörnet för att läsa mer om boken.
Joe, från Kalifornien, plus många andra har bett om ett sätt att spara varje kalkylblad i en annan fil, antingen som en PDF i Joe: s fall eller som en Excel-fil om du ska använda Power Query för att kombinera filer. Så jag skapade ett trevligt litet Generic Freeware Utility där ute. Du kan ladda ner och ge det en chans.
Jag vill tacka Joe för att han ställde den frågan och vill tacka dig för att du kom förbi. Vi ses nästa gång för en ny netcast från.
Nedladdning fil
Ladda ner exempelfilen här: Podcast2107.xlsm
