Masz skoroszyt z wieloma arkuszami. Chcesz wysłać każdy arkusz do innej osoby. Dziś makro do podziału tych danych.
Obejrzyj wideo
- Joe i inni szukają sposobu na zapisanie każdego arkusza w innym pliku
- Przydatne w przypadku dodatku Power Query lub po użyciu opcji Pokaż strony filtrów raportów
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2107 - Podziel każdy arkusz na nowy skoroszyt
Witaj z powrotem. Jestem i netcast. Jestem Bill Jelen.
W głębi mojej głowy wiedziałem, że muszę to zrobić od dłuższego czasu, ale dwa ostatnie podcasty naprawdę to przyniosły, przyniosły to do domu.
Niedawno w odcinku 2106, w którym tworzyliśmy plik PDF z All Slicer Combinations. Pod koniec tego odcinka pokazałem alternatywną metodę, w której tworzymy wiele raportów przestawnych, ale umieszcza je wszystkie w tym samym skoroszycie i otrzymałem e-mail od Joe z Kalifornii mówiący dobrze, spójrz, muszę wysłać każdy arkusz roboczy do innego klienta i to samo, w moich seminariach Power Excel na żywo, na których pokazuję tę sztuczkę, ludzie mówią: cóż, nie, nie chcemy tego wszystkiego w tym samym skoroszycie, chcemy go osobno i prawdopodobnie nawet ważniejsze niż to, wraca odcinek 2077, w którym mówiłem o tym, jak dodatek Power Query ma teraz możliwość łączenia wszystkich plików programu Excel w folderze, prawda? I to jest cudowne. Działa świetnie. Jeśli masz 400 plików Excela, każdy z jednym arkuszem roboczym, pobierze wszystkie dane ze wszystkich tych arkuszy i umieści je w jednej siatce.Co jest niesamowite, ale gdybyśmy mieli prawie ten sam problem. Jeden skoroszyt z 400 arkuszami roboczymi? Nie może tego zrobić, prawda. Nie może sobie z tym poradzić - jeszcze. Tak, więc teraz, 1 lipca 2017, nie może sobie z tym poradzić. Może za sześć miesięcy sobie z tym poradzi, ale teraz muszą to być skoroszyty na jednym arkuszu.
Potrzebujemy więc sposobu, aby móc podzielić rzeczy na pojedyncze pliki. Ok, więc ustawmy to. Mamy skoroszyt, który zrobiłem w 2106, w którym mamy dane, a następnie oryginalną tabelę przestawną i przechodzimy do analizy, opcji, pokazania stron filtrów raportów i pokazania stron klucza, i tworzy on dla mnie całą masę różnych arkuszy roboczych i Chcę wziąć te Arkusze i utworzyć każdy z nich w osobnym pliku, ale mimo że go mamy, jest kilka rzeczy, takich jak Arkusz2 i Dane, których nie chcę dzielić.
W porządku? I oczywiście dla każdej osoby te rzeczy, te, ta lista Arkuszy, których nie chcemy dzielić, będą inne, ale zgadnę, że prawie każdy ma jakieś Arkusze, których nie ma. nie chcę się rozdzielić.
W porządku, więc oto narzędzie, które będziesz mógł pobrać. Dzielnik arkuszy roboczych i tutaj mam sekcję w kolumnie B i tak naprawdę jest to jedyna rzecz w kolumnie B, w której możesz wymienić te arkusze, których nie chcesz dzielić. Może być więcej niż dwa. Możesz wpisać tyle, ile chcesz. Możesz wstawić nowe wiersze i mój tani sposób, nie chciałem przechodzić przez nie w pętli w makro, tak daleko poza twoim widokiem tutaj, mam miejsce, w którym makro może zapisać bieżącą nazwę arkusza roboczego, a następnie prosty mały WYSZUKAJ.PIONOWO. Mówi, poszukaj tego arkusza roboczego, nad którym teraz pracujemy, zobacz, czy skończył się w kolumnie B, a jeśli tak, będziemy wiedzieć, że to jest ten, którego nie chcemy eksportować.
W porządku, a potem jeszcze raz, aby uczynić to tak ogólnym, jak to tylko możliwe, mam tutaj kilka nazwanych zakresów, moją ścieżkę, mój prefiks, mój sufiks, mój typ i wklej. W porządku, więc musisz dowiedzieć się, gdzie chcesz, aby te rzeczy poszły. c: Raporty . Chcę, aby każdy plik miał nazwę arkusza, ale przed nazwą arkusza wstawię przedrostek WB, Sufiks pliku i nic, a następnie masz tutaj wybór: PDF lub XLSX.
Więc zaczniemy od XLSX, porozmawiamy o tych wartościach wklejania przed zapisaniem później. W porządku, teraz jest to pierwsza wersja z 1 lipca 2017 roku. Jeśli to poprawimy, po prostu zastąpię to na stronie internetowej, a stronę internetową znajdziesz tam w opisach YouTube. W porządku, więc oto jak to będzie działać. To plik XLSM. Musisz więc upewnić się, że makra są dozwolone. alt = "" T, M, S, ze względów bezpieczeństwa musisz być przynajmniej na tym lub niższym poziomie. Jeśli jesteś na górze, musisz zmienić, zamknąć skoroszyt, ponownie otworzyć. Kiedy otworzysz skoroszyt, powie, hej, czy jesteś gotów zaakceptować tutaj makra i nie jest to wcale duże makro: sześćdziesiąt osiem linii kodu, a wiele z nich dotyczy tylko usuwania wartości z menu Arkusz,jakie są teraz zmienne.
Ważną rzeczą jest jednak to, że będzie działać na ActiveWorkbook. Więc zamierzasz przełączyć się do skoroszytu, który zawiera dane, a następnie nacisnąć CTRL SHIFT S, aby go uruchomić, a on wykryje ActiveWorkbook i to będzie ten, który dzieli. Przechwytuje („MyPath”) i to tylko dlatego, że zawsze zapominam o wstawieniu tego ukośnika odwrotnego, jeśli ostatni znak nie jest ukośnikiem odwrotnym, to dodam ukośnik odwrotny, a na dole to jest właściwa praca.
Dla każdego arkusza roboczego, w oryginale, w aktywnym WBO. Arkusze robocze sprawdzimy, czy jest to arkusz, który jest tam i kolumna B. Jeśli tak, jeśli nie, to wyeksportujemy ten arkusz, a ja uwielbiam tę linię kodu. WS.copy mówi, że kiedy biorę ten skoroszyt, ten arkusz roboczy, z tego dużego skoroszytu, znasz 20 lub 400 arkuszy roboczych i idziemy do WS.copy, który tworzy jego kopię i przenosi go do nowego skoroszytu i wiemy, wiemy, że ten nowy skoroszyt stanie się teraz aktywnym skoroszytem w makrze i oczywiście jest tylko jeden arkusz w tym skoroszycie, a ten arkusz jest aktywnym arkuszem.
So right, here I can figure out the name of the Workbook. Set it, Apply to This Object Variable, Workbook New, Worksheet New and then later on, when I have to close, I can do WBN.close after I've done the work. We figure out the new file name using all of the variables. Kill that file, if it already exists and then, if it's an Excel file, we do a save as, if it's a PDF.
And by the way this PDF code only works in Windows, if you're on a Mac, sorry, you're going to have to go somewhere else to figure out the equivalent Mac code. I don't have a Mac. I know there's a way to save a PDF on a Mac. I know the code is different. You'll have to figure that one out or come back to the real Excel in Windows and then we're done, we close.
Alright, so it's just a simple little Macro like that, switch over to our data Workbook here, the one that has all the Worksheets. There's 20 different Worksheets here, plus the two I don't want to do and then CTRL SHIFT S like this and we'll watch it flash as it creates each one. There we are: 21 files created.
Let's go take a look in Windows Explorer and here's my OS (C:) Reports, it created for each Worksheet, named in the original Workbook it created a new version with WB up front. Alright now, Joe, when Joe sent me this note, he said he's going to send this data to customers and I kind of panicked at first because I said, wait a second Joe, we're going to have a problem because you're going to send Gary, his data, right? But this is, ah, you know a live, a live data set, it's a live Pivot Table. All of the stuff in here, you might be able to get all the information for other customers like that, right? Boy, you don't want to send a customer A the information for all of the other customers. That could be a hassle and actually, when I reread the shows note, he was smarter than me, because he said: I want to create them as PDF files. I was like, alright, well then yeah, we don't have to worry about as PDF files, that's fine, but what I added in here, to the Macro was the ability to say Paste Values Before Saving? TRUE.
So you set that equal to TRUE and that's going to invoke this tiny bit of code here, where we say, If PasteV Then the UsedRange.Copy and then UsedRange.PasteSpecial (xlPasteValues), UsedRange, rather than copying and pasting all 17 billion cells, it'll limit it down to well, the UsedRange.
Alright, so let's switch back, switch those Worksheet that has the data, CTRL SHIFT S for split and then this new version in the reports directory, you'll see that it has gotten rid of the Pivot Table and left just the data there. So that way they can't get to all data.
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 z Kalifornii, a także wielu innych osób prosiło o możliwość zapisania każdego arkusza roboczego w innym pliku, albo jako plik PDF w przypadku Joe, albo jako plik programu Excel, na wypadek, gdybyś używał dodatku Power Query do łączenia plików. Stworzyłem więc ładne, małe narzędzie Generic Freeware Utility. Możesz pobrać i spróbować.
Chcę podziękować Joe za postawienie tego pytania i podziękować za zatrzymanie się. Do zobaczenia następnym razem z kolejnym netcastem od.
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2107.xlsm
