Split Workbook by Worksheets

Learn Excel from MrExcel Podcast, Episode 2107 -- Split each worksheet to a new Workbook

Hey welcome back. I'm MrExcel and netcast. I'm Bill Jelen.

I've known in the back of my head that I've needed to do this for a long time, but two recent podcasts really brought it, brought it home.

Just recently in episode 2106, where we were creating a PDF from All Slicer Combinations. Late in that episode I showed an alternate method where we create many pivot reports, but it puts them all on the same Workbook and I got an email from Joe in California says well, look I need to send each Worksheet to a different customer, and same thing, in my live Power Excel Seminars where I show that trick, people say, well no, we don't want it all in the same Workbook, we we want it separately and then probably even more important than that, is back in episode 2077, where I talked about how Power Query now has the ability to combine all of the Excel files in a folder, right? And this is miraculous. It works great. If you had 400 Excel files, each with a single Worksheet, it will grab all that data from all those Worksheets and put it in one grid. Which is awesome, but if we had almost the same problem. One Workbook with 400 Worksheets? It can't do it, right. It can't deal with that - yet. Right, so right now, July 1st 2017, it can't deal with that. Maybe in six months it can deal with that, but right now, it has to be single sheet Workbooks.

So we need a way to be able to break things out into individual files. Okay, so let's let's just set this up. We have the Workbook I did in 2106 where we have the data and then the original Pivot Table and we go into Analyze, Options, Show Report Filter Pages and show pages of the key, and it creates a whole bunch of different Worksheets for me and I want to take those Worksheets and create each one is a separate file, but even though we have that, there's some stuff like Sheet2 and Data that I don't want to split.

Alright? And of course for every single person, that stuff, those, that list of Worksheets, that we don't want to split, is going to be different, but I'm going to guess that almost everyone has some Worksheets that they don't want to split.

Alright, so here's the utility that you're going to be able to download. The MrExcel Worksheets Splitter and over here I have a section in column B and it's really the only thing in column B where you can list those Worksheets that you don't want to split. It can be more than two. You can fill in as many here as you want. You can insert new rows and my cheap way, I didn't want to have to loop through these in the Macro, so far outside of your view out here, I have a spot where the Macro can write the current Worksheet name and then a simple little VLOOKUP. It says, go look for this Worksheet that we're working on right now, see if it's over in column B and and if it is, we'll know that that's one that we don't want to export.

Alright and then again just to make this as generic as possible, I've got several named ranges here, my Path, my Prefix, my Suffix,  my Type and my Paste. Alright, so you figure out where you want this stuff to go. c:\Reports\. I want every file to have the sheet name, but before the sheet name, I'm going to put the prefix of WB, File Suffix and nothing and then you have a choice here: PDF or XLSX.

So we're going to start off with the XLSX, we'll talk about this paste values before saving later. Alright and right now this is version July 1st 2017, the first one. If we improve this, I'll just replace it out on the the webpage and you can find the webpage down there in the YouTube descriptions. Alright so here's how this is going to work. It's an XLSM file. So you have to make sure that Macros are allowed. ALT T, M, S, for security you have to be at least at this level or below. Right if you're at the top one, you need to change, it close the Workbook, reopen. When you open the Workbook, it's going to say, hey are you willing to accept the Macros here and it's not a big Macro at all: sixty-eight lines of code and a lot of that is just dealing with getting the values off the Menu Sheet, what are the variables now.

The important thing here though is that it's going to work on the ActiveWorkbook. So you're going to switch to the Workbook that has the data and then press CTRL SHIFT S to run it and it's going to detect the ActiveWorkbook and that's going to be the one that it splits out. It grabs (“MyPath”) and it's just because I always forget to put that backslash, if the last character is not a backslash, then I'm going to add a backslash and then down here this is the actual work.

For each Worksheet, in the original, in the active WBO.Worksheets, we're going to test to see if it's one that's over there and column B. If it's, if it's not then we're going to Export this sheet and I love this line of code. WS.copy says, when I take this Workbook, this Worksheet, from this big Workbook with, you know 20 or 400 Worksheets and we're going to WS.copy, which makes a copy of it and moves it to a new work Workbook and we know, we know that that new Workbook will now become the active Workbook in the Macro and of course there's only one sheet in that Workbook and that sheet is the active sheet.

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 MrExcel.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, from California, plus a lot of others have asked for a way to save each Worksheet to a different file, either as a PDF in Joe's case or an Excel file in case you're going to use Power Query to combine files. So I created a nice little Generic Freeware Utility out there. You can download and give it a shot.

I want to thank Joe for setting that question and want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.