Anda memiliki buku kerja dengan banyak lembar kerja. Anda ingin mengirim setiap lembar kerja ke orang yang berbeda. Hari ini, makro untuk memisahkan data itu.
Menonton video
- Joe + Others sedang mencari cara untuk menyimpan setiap lembar kerja ke file yang berbeda
- Berguna untuk Power Query atau setelah menggunakan Perlihatkan Halaman Filter Laporan
Transkrip Video
Pelajari Excel dari Podcast, Episode 2107 - Pisahkan setiap lembar kerja ke Buku Kerja baru
Hei selamat datang kembali. Saya dan netcast. Saya Bill Jelen.
Saya sudah tahu di belakang kepala saya bahwa saya perlu melakukan ini untuk waktu yang lama, tetapi dua podcast baru-baru ini benar-benar membawanya, membawanya pulang.
Baru-baru ini di episode 2106, di mana kami membuat PDF dari All Slicer Combinations. Di akhir episode itu saya menunjukkan metode alternatif di mana kami membuat banyak laporan pivot, tetapi itu menempatkan semuanya di Buku Kerja yang sama dan saya mendapat email dari Joe di California mengatakan baik, lihat saya perlu mengirim setiap Lembar Kerja ke pelanggan yang berbeda, dan hal yang sama, di Seminar Power Excel saya secara langsung di mana saya menunjukkan trik itu, orang-orang berkata, tidak, kami tidak ingin semuanya ada di Buku Kerja yang sama, kami menginginkannya secara terpisah dan mungkin bahkan lebih penting dari itu, sudah kembali episode 2077, di mana saya berbicara tentang bagaimana Power Query sekarang memiliki kemampuan untuk menggabungkan semua file Excel dalam sebuah folder, bukan? Dan ini ajaib. Ini bekerja dengan baik. Jika Anda memiliki 400 file Excel, masing-masing dengan satu Lembar Kerja, itu akan mengambil semua data dari semua Lembar Kerja tersebut dan memasukkannya ke dalam satu kotak.Luar biasa, tetapi jika kita memiliki masalah yang hampir sama. Satu Buku Kerja dengan 400 Lembar Kerja? Itu tidak bisa, benar. Itu belum bisa ditangani - belum. Baiklah, jadi sekarang, 1 Juli 2017, tidak bisa mengatasinya. Mungkin dalam enam bulan bisa mengatasinya, tapi saat ini, itu harus menjadi Buku Kerja satu lembar.
Jadi kami membutuhkan cara untuk dapat memecah hal-hal menjadi file individual. Oke, jadi mari kita atur ini. Kami memiliki Buku Kerja yang saya buat pada tahun 2106 di mana kami memiliki data dan kemudian Tabel Pivot asli dan kami masuk ke Analisis, Opsi, Tampilkan Halaman Filter Laporan dan tampilkan halaman kunci, dan itu membuat sejumlah besar Lembar Kerja yang berbeda untuk saya dan Saya ingin mengambil Lembar Kerja itu dan membuat masing-masing adalah file terpisah, tetapi meskipun kita memilikinya, ada beberapa hal seperti Sheet2 dan Data yang tidak ingin saya pisahkan.
Baik? Dan tentu saja untuk setiap orang, hal-hal, itu, daftar Lembar Kerja itu, yang tidak ingin kami pisahkan, akan berbeda, tapi saya akan menebak bahwa hampir setiap orang memiliki beberapa Lembar Kerja yang mereka tidak ' Aku tidak ingin berpisah.
Baiklah, inilah utilitas yang dapat Anda unduh. Pemisah Lembar Kerja dan di sini saya memiliki bagian di kolom B dan itu benar-benar satu-satunya hal di kolom B di mana Anda dapat membuat daftar Lembar Kerja yang tidak ingin Anda pisahkan. Bisa lebih dari dua. Anda dapat mengisi sebanyak yang Anda inginkan di sini. Anda dapat memasukkan baris baru dan cara saya yang murah, saya tidak ingin mengulanginya di Makro, sejauh ini di luar pandangan Anda di sini, saya memiliki tempat di mana Makro dapat menulis nama Lembar Kerja saat ini dan kemudian a VLOOKUP kecil yang sederhana. Dikatakan, cari Worksheet ini yang sedang kita kerjakan sekarang, lihat apakah sudah berakhir di kolom B dan dan jika sudah, kita akan tahu bahwa itu adalah salah satu yang tidak ingin kita ekspor.
Baiklah dan sekali lagi hanya untuk membuat ini generik mungkin, saya punya beberapa rentang bernama di sini, Path saya, Awalan saya, Suffix saya, Jenis saya dan Tempel saya. Baiklah, jadi Anda mencari tahu ke mana Anda ingin barang ini pergi. c: Laporan . Saya ingin setiap file memiliki nama sheet, tetapi sebelum nama sheet, saya akan meletakkan awalan WB, File Suffix dan tidak ada dan kemudian Anda punya pilihan di sini: PDF atau XLSX.
Jadi kita akan mulai dengan XLSX, kita akan membicarakan tentang nilai tempel ini sebelum menyimpannya nanti. Oke dan sekarang ini versi 1 Juli 2017 yang pertama. Jika kami meningkatkan ini, saya akan menggantinya di halaman web dan Anda dapat menemukan halaman web di bawah sana dalam deskripsi YouTube. Baiklah jadi begini cara kerjanya. Ini file XLSM. Jadi, Anda harus memastikan bahwa Makro diizinkan. alt = "" T, M, S, untuk keamanan Anda harus setidaknya berada di level ini atau di bawahnya. Tepat jika Anda berada di atas, Anda perlu mengubahnya, tutup Buku Kerja, buka kembali. Saat Anda membuka Buku Kerja, akan muncul pesan, hei apakah Anda bersedia menerima Makro di sini dan ini sama sekali bukan Makro yang besar: enam puluh delapan baris kode dan banyak di antaranya hanya berurusan dengan mengeluarkan nilai dari Menu Lembar,apa variabelnya sekarang.
Hal yang penting di sini adalah bahwa ini akan berfungsi di ActiveWorkbook. Jadi Anda akan beralih ke Buku Kerja yang memiliki datanya dan kemudian tekan CTRL SHIFT S untuk menjalankannya dan itu akan mendeteksi Buku Kerja yang akan menjadi buku yang dipisahkan. Ini mengambil ("MyPath") dan itu hanya karena saya selalu lupa untuk meletakkan garis miring terbalik, jika karakter terakhir bukan garis miring terbalik, maka saya akan menambahkan garis miring terbalik dan kemudian di sini ini adalah pekerjaan yang sebenarnya.
Untuk setiap Lembar Kerja, aslinya, di WBO. Lembar Kerja yang aktif, kami akan menguji untuk melihat apakah itu ada di sana dan kolom B. Jika, jika tidak, maka kami akan Mengekspor lembar ini dan saya suka baris kode ini. WS.copy mengatakan, ketika saya mengambil Buku Kerja ini, Lembar Kerja ini, dari Buku Kerja besar ini dengan, Anda tahu 20 atau 400 Lembar Kerja dan kami akan pergi ke WS.copy, yang membuat salinannya dan memindahkannya ke Buku Kerja baru dan kita tahu, kita tahu bahwa Buku Kerja baru itu sekarang akan menjadi Buku Kerja aktif di Makro dan tentu saja hanya ada satu lembar di Buku Kerja itu dan lembar itu adalah lembar aktif.
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, dari California, dan banyak orang lainnya telah meminta cara untuk menyimpan setiap Lembar Kerja ke file yang berbeda, baik sebagai PDF dalam kasus Joe atau file Excel jika Anda akan menggunakan Power Query untuk menggabungkan file. Jadi saya membuat Utilitas Freeware Generik kecil yang bagus di luar sana. Anda dapat mengunduh dan mencobanya.
Saya ingin berterima kasih kepada Joe karena telah mengajukan pertanyaan itu dan ingin berterima kasih karena telah mampir. Sampai jumpa di lain waktu untuk netcast lain dari.
Unduh berkas
Unduh file contoh di sini: Podcast2107.xlsm
