Bạn có một sổ làm việc với nhiều trang tính. Bạn muốn gửi mỗi trang tính cho một người khác. Hôm nay, một macro để tách dữ liệu đó ra.
Xem video
- Joe + Những người khác đang tìm cách lưu từng trang tính vào một tệp khác
- Hữu ích cho Power Query hoặc sau khi sử dụng Hiển thị Trang Bộ lọc Báo cáo
Bản ghi video
Học Excel từ Podcast, Tập 2107 - Tách từng trang tính thành một Sổ làm việc mới
Chào mừng trở lại. Tôi và netcast. Tôi là Bill Jelen.
Tôi đã biết trong đầu rằng tôi cần phải làm điều này từ lâu, nhưng hai podcast gần đây đã thực sự mang nó, mang nó về nhà.
Gần đây, trong tập 2106, nơi chúng tôi đang tạo một tệp PDF từ Tất cả các tổ hợp Slicer. Cuối tập đó, tôi đã chỉ ra một phương pháp thay thế trong đó chúng tôi tạo nhiều báo cáo tổng hợp, nhưng nó đặt tất cả chúng trên cùng một Sổ làm việc và tôi nhận được email từ Joe ở California nói rằng tốt, tôi cần gửi từng Bảng tính cho một khách hàng khác và điều tương tự, trong Hội thảo Power Excel trực tiếp của tôi, nơi tôi chỉ ra thủ thuật đó, mọi người nói, không, chúng tôi không muốn tất cả trong cùng một Sổ làm việc, chúng tôi muốn nó riêng biệt và sau đó có lẽ còn quan trọng hơn thế, tập 2077, nơi tôi đã nói về cách Power Query hiện có khả năng kết hợp tất cả các tệp Excel trong một thư mục, phải không? Và điều này thật kỳ diệu. Nó hoạt động tuyệt vời. Nếu bạn có 400 tệp Excel, mỗi tệp có một Trang tính, nó sẽ lấy tất cả dữ liệu đó từ tất cả các Trang tính đó và đặt nó trong một lưới.Điều đó thật tuyệt vời, nhưng nếu chúng tôi gặp vấn đề gần như tương tự. Một Workbook với 400 Worksheets? Nó không thể làm điều đó, đúng. Nó không thể đối phó với điều đó - chưa. Đúng vậy, ngay bây giờ, ngày 1 tháng 7 năm 2017, nó không thể giải quyết việc đó. Có thể trong sáu tháng nữa nó có thể giải quyết được điều đó, nhưng ngay bây giờ, nó phải là Workbooks đơn.
Vì vậy, chúng tôi cần một cách để có thể chia nhỏ mọi thứ thành các tệp riêng lẻ. Được rồi, chúng ta hãy thiết lập điều này. Chúng tôi có Workbook mà tôi đã làm vào năm 2106, nơi chúng tôi có dữ liệu và sau đó là Pivot Table ban đầu và chúng tôi đi vào Phân tích, Tùy chọn, Hiển thị Trang Bộ lọc Báo cáo và hiển thị các trang của khóa và nó tạo ra một loạt các Trang tính khác nhau cho tôi và Tôi muốn lấy các Trang tính đó và tạo mỗi Trang tính là một tệp riêng biệt, nhưng mặc dù chúng tôi có điều đó, nhưng có một số thứ như Trang tính2 và Dữ liệu mà tôi không muốn tách.
Ổn thỏa? Và tất nhiên đối với mỗi người, những thứ đó, những thứ đó, danh sách Bảng tính mà chúng tôi không muốn tách ra, sẽ khác nhau, nhưng tôi đoán rằng hầu hết mọi người đều có một số Bảng tính mà họ không có ' t muốn chia rẽ.
Được rồi, đây là tiện ích mà bạn có thể tải xuống. Worksheets Splitter và ở đây tôi có một phần ở cột B và nó thực sự là thứ duy nhất trong cột B nơi bạn có thể liệt kê những Worksheets mà bạn không muốn tách. Nó có thể nhiều hơn hai. Bạn có thể điền bao nhiêu vào đây tùy thích. Bạn có thể chèn các hàng mới và theo cách rẻ tiền của tôi, tôi không muốn phải lặp lại các hàng này trong Macro, cho đến nay bên ngoài tầm nhìn của bạn ở đây, tôi có một chỗ mà Macro có thể viết tên Trang tính hiện tại và sau đó VLOOKUP nhỏ đơn giản. Nó nói rằng, hãy tìm Trang tính mà chúng tôi đang làm ngay bây giờ, xem nó có hết ở cột B hay không và nếu có, chúng tôi sẽ biết rằng đó là một bảng mà chúng tôi không muốn xuất.
Được rồi và sau đó chỉ để làm cho điều này càng chung chung càng tốt, tôi có một số phạm vi được đặt tên ở đây, Đường dẫn của tôi, Tiền tố của tôi, Hậu tố của tôi, Loại của tôi và Dán của tôi. Được rồi, vậy là bạn đã tìm ra nơi bạn muốn nội dung này đi. c: Báo cáo . Tôi muốn mọi tệp có tên trang tính, nhưng trước tên trang tính, tôi sẽ đặt tiền tố WB, File Suffix và không có gì và sau đó bạn có một sự lựa chọn ở đây: PDF hoặc XLSX.
Vì vậy, chúng ta sẽ bắt đầu với XLSX, chúng ta sẽ nói về các giá trị dán này trước khi lưu sau. Được rồi và ngay bây giờ đây là phiên bản ngày 1 tháng 7 năm 2017, phiên bản đầu tiên. Nếu chúng tôi cải thiện điều này, tôi sẽ chỉ thay thế nó trên trang web và bạn có thể tìm thấy trang web dưới đó trong phần mô tả YouTube. Được rồi, đây là cách nó sẽ hoạt động. Đó là một tệp XLSM. Vì vậy, bạn phải đảm bảo rằng Macro được phép. alt = "" T, M, S, để bảo mật, bạn phải ít nhất ở cấp độ này hoặc thấp hơn. Ngay nếu bạn đang ở trên cùng, bạn cần thay đổi, nó đóng Workbook, mở lại. Khi bạn mở Workbook, nó sẽ nói rằng, bạn có sẵn sàng chấp nhận Macro ở đây không và nó không phải là một Macro lớn chút nào: sáu mươi tám dòng mã và rất nhiều trong số đó chỉ giải quyết việc lấy các giá trị ra khỏi Menu Tấm,các biến bây giờ là gì.
Điều quan trọng ở đây là nó sẽ hoạt động trên ActiveWorkbook. Vì vậy, bạn sẽ chuyển sang Workbook có dữ liệu và sau đó nhấn CTRL SHIFT S để chạy nó và nó sẽ phát hiện ActiveWorkbook và đó sẽ là cái mà nó tách ra. Nó lấy (“MyPath”) và chỉ vì tôi luôn quên đặt dấu gạch chéo ngược đó, nếu ký tự cuối cùng không phải là dấu gạch chéo ngược, thì tôi sẽ thêm một dấu gạch chéo ngược và sau đó đây là tác phẩm thực tế.
Đối với mỗi Worksheet, trong bản gốc, trong WBO. yêu dòng mã này. WS.copy cho biết, khi tôi lấy Workbook này, Worksheet này, từ Workbook lớn này với, bạn biết 20 hoặc 400 Worksheets và chúng tôi sẽ đến WS.copy, nơi tạo một bản sao của nó và chuyển nó sang Workbook mới và chúng tôi biết, chúng tôi biết rằng Workbook mới đó bây giờ sẽ trở thành Workbook hoạt động trong Macro và tất nhiên chỉ có một sheet trong Workbook đó và sheet đó là sheet đang hoạt động.
Vì vậy, ở đây tôi có thể tìm ra tên của Workbook. Đặt nó, Áp dụng cho Biến đối tượng này, Sổ làm việc Mới, Trang tính Mới và sau đó, khi tôi phải đóng, tôi có thể thực hiện WBN.close sau khi hoàn thành công việc. Chúng tôi tìm ra tên tệp mới bằng cách sử dụng tất cả các biến. Hủy tệp đó, nếu nó đã tồn tại và sau đó, nếu đó là tệp Excel, chúng tôi thực hiện lưu dưới dạng, nếu đó là tệp PDF.
Và bằng cách này, mã PDF này chỉ hoạt động trong Windows, nếu bạn đang sử dụng máy Mac, xin lỗi, bạn sẽ phải đến một nơi khác để tìm ra mã Mac tương đương. Tôi không có máy Mac. Tôi biết có một cách để lưu tệp PDF trên máy Mac. Tôi biết mã là khác nhau. Bạn sẽ phải tìm ra điều đó hoặc quay lại Excel thực trong Windows và sau đó chúng tôi hoàn thành, chúng tôi đóng.
Được rồi, vì vậy nó chỉ là một Macro nhỏ đơn giản như vậy, hãy chuyển sang Sổ làm việc dữ liệu của chúng tôi ở đây, sổ có tất cả các Trang tính. Có 20 Bảng tính khác nhau ở đây, cộng với hai Bảng tính tôi không muốn làm và sau đó CTRL SHIFT S như thế này và chúng ta sẽ xem nó nhấp nháy khi nó tạo ra từng bảng. Chúng tôi có: 21 tệp đã được tạo.
Hãy xem trong Windows Explorer và đây là Báo cáo OS (C :) của tôi, nó được tạo cho mỗi Trang tính, có tên trong Sổ làm việc ban đầu, nó đã tạo một phiên bản mới với WB ở phía trước. Được rồi, Joe, khi Joe gửi cho tôi ghi chú này, anh ấy nói rằng anh ấy sẽ gửi dữ liệu này cho khách hàng và lúc đầu tôi hơi hoảng vì tôi đã nói, chờ một chút Joe, chúng ta sẽ gặp sự cố vì bạn sẽ gửi cho Gary, dữ liệu của anh ấy, phải không? Nhưng đây là, ah, bạn biết một tập dữ liệu trực tiếp, trực tiếp, đó là một Bảng Pivot trực tiếp. Tất cả những thứ trong đây, bạn có thể có được tất cả thông tin cho những khách hàng khác như vậy, phải không? Chàng trai, bạn không muốn gửi thông tin cho một khách hàng A cho tất cả các khách hàng khác. Đó có thể là một rắc rối và thực sự, khi tôi đọc lại ghi chú chương trình, anh ấy thông minh hơn tôi, bởi vì anh ấy nói:Tôi muốn tạo chúng dưới dạng tệp PDF. Tôi đã nói, được rồi, vâng, chúng ta không phải lo lắng về việc làm tệp PDF, điều đó tốt, nhưng những gì tôi đã thêm ở đây, vào Macro là khả năng nói Dán Giá trị Trước khi Lưu? THẬT.
Vì vậy, bạn đặt giá trị đó bằng TRUE và điều đó sẽ gọi ra một đoạn mã nhỏ này ở đây, nơi chúng ta nói, If PasteV thì là WasRange.Copy và sau đó là usedRange.PasteSpecial (xlPasteValues), usedRange, thay vì sao chép và dán tất cả 17 tỷ ô , nó sẽ giới hạn nó xuống rất tốt, WasRange.
Được rồi, chúng ta hãy quay lại, chuyển Bảng tính có dữ liệu, CTRL SHIFT S để tách và sau đó là phiên bản mới này trong thư mục báo cáo, bạn sẽ thấy rằng nó đã loại bỏ Bảng Pivot và chỉ để lại dữ liệu ở đó. Vì vậy, theo cách đó họ không thể truy cập tất cả dữ liệu.
Được rồi, chúng tôi sẽ thử tính năng khác. Chúng tôi sẽ thử nếu chúng tôi chuyển từ Excel sang PDF thay đổi tiền tố thành PDFFileOf, bất cứ điều gì chúng tôi muốn ở đó. Tôi thậm chí sẽ không thử hậu tố, một cái gì đó. Được rồi, sau đó chuyển sang dữ liệu, CTRL SHIFT S. Được rồi, chúng ta sẽ nhận được các tệp giống nhau PDFFileNếu tên Bảng tính, một cái gì đó của PDF và chúng ta sẽ có những tệp PDF nhỏ xinh xắn trong đó, như vậy.
Được rồi, bạn đã có nó the.com Worksheet Splitter. Hy vọng rằng đủ chung chung, cho bất cứ điều gì bạn cần. Tải xuống lại từ liên kết ở đó trong phần nhận xét trên YouTube. Để tìm hiểu thêm về VBA, hãy xem cuốn sách Excel 2016 VBA và Macro này của tôi và Tracy? 08: 50.640. Nhấp vào Tôi ở góc trên cùng bên phải, để đọc thêm về cuốn sách.
Joe, đến từ California, cộng với nhiều người khác đã yêu cầu cách lưu mỗi Trang tính vào một tệp khác nhau, dưới dạng PDF trong trường hợp của Joe hoặc tệp Excel trong trường hợp bạn định sử dụng Power Query để kết hợp các tệp. Vì vậy, tôi đã tạo ra một Tiện ích phần mềm miễn phí chung nhỏ rất hay ở đó. Bạn có thể tải xuống và thử.
Tôi muốn cảm ơn Joe vì đã đặt câu hỏi đó và muốn cảm ơn bạn đã ghé qua. Chúng tôi sẽ gặp bạn lần sau cho một netcast khác từ.
Tải tập tin
Tải file mẫu tại đây: Podcast2107.xlsm
