Rene from Norway asks how to modify the code from podcast 730 in order to create a new workbook for every department. Episode 754 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Alright. Welcome back to the MrExcel netcast. I’m Bill Jelen.
Well, we have a question today sent in from Norway.
Rene wrote in and said, hey, I just found your podcast. It's excellent.
I was watching episode 730 where you had a list of all of these departments and you were able to create one worksheet for every department.
He says, what I need to do is very similar but I need to create one workbook for every department.
Alright.
So, let's go and take a look at the code here.
I’m going to switch over to VBA, and the code, I've already modified it a little bit.
Before, we said, FOR EACH CELL IN SELECTION, and basically everything from this line down to NEXT CELL is going to be part of the loop, and, this time, what I'm going to do is assume that there is some workbook template.
I don't think we want to just create a 100 hundred blank workbook.
So, I assume that they've gone through and set up some workbook, and, in this case, it might be called TEMPLATE.
So, I say WORKSHEETS, then in quotes, TEMPLATE . COPY.
Now, when we just do a .COPY on a worksheet, what it does is it makes a copy and it moves it out to a new workbook, and, automatically, that new workbook becomes the active workbook.
So, now I have that active workbook, I’ll do .SAVEAS, and I'm going to concatenate using the &, C:, and the folder I want to go to, AAA\, and then whatever the cell value was.
So, each department will have its own unique name based on the department number, and then .XLS.
Think about that.
Now that I've saved it, it is still the active workbook.
I want to close it, so ACTIVEWORKBOOK.CLOSE will do what we need to do, and then it'll go back and grab the next cell.
So, let's try this real quick.
Here.
Let’s just try a few cells and see how it works, and I'm going to step through.
I want to step through and test this code.
To step through, we press the F8 key.
Basically, each time I press F8, it’s going to run the line that is in yellow.
So, I want to make sure that this actually works -- WORKSHEETS TEMPLATE.COPY -- and, sure enough, when I switch over to Excel, you'll see that we are now in book one.
It created a brand new book with all the data from my template.
Now, in this case, I just have just crazy…just the word data over and over and over.
It'll save it, close it.
Looks good.
Let's go ahead and run that, and you can see on the left-hand side there in the PROJECT explorer it kind of flashes.
Now, we'll try it again.
Let me choose all the remaining departments here.
I’ll press ALT+F8 to see a list of those and I'll click TEST, click RUN, and it actually takes a while to run because it has to go through and create about 60 different workbooks, but what we should see is, I switch over to the folder, the AAA folder, if I refresh this, VIEW, REFRESH, you'll see that it's actually creating workbooks.
There's some more, and so on.
In less than a minute, I will have created 60 copies of the workbook, each with the unique name.
Well, there you have it.
Thanks for stopping by.
We’ll see you next time for another netcast from MrExcel.
Well, we have a question today sent in from Norway.
Rene wrote in and said, hey, I just found your podcast. It's excellent.
I was watching episode 730 where you had a list of all of these departments and you were able to create one worksheet for every department.
He says, what I need to do is very similar but I need to create one workbook for every department.
Alright.
So, let's go and take a look at the code here.
I’m going to switch over to VBA, and the code, I've already modified it a little bit.
Before, we said, FOR EACH CELL IN SELECTION, and basically everything from this line down to NEXT CELL is going to be part of the loop, and, this time, what I'm going to do is assume that there is some workbook template.
I don't think we want to just create a 100 hundred blank workbook.
So, I assume that they've gone through and set up some workbook, and, in this case, it might be called TEMPLATE.
So, I say WORKSHEETS, then in quotes, TEMPLATE . COPY.
Now, when we just do a .COPY on a worksheet, what it does is it makes a copy and it moves it out to a new workbook, and, automatically, that new workbook becomes the active workbook.
So, now I have that active workbook, I’ll do .SAVEAS, and I'm going to concatenate using the &, C:, and the folder I want to go to, AAA\, and then whatever the cell value was.
So, each department will have its own unique name based on the department number, and then .XLS.
Think about that.
Now that I've saved it, it is still the active workbook.
I want to close it, so ACTIVEWORKBOOK.CLOSE will do what we need to do, and then it'll go back and grab the next cell.
So, let's try this real quick.
Here.
Let’s just try a few cells and see how it works, and I'm going to step through.
I want to step through and test this code.
To step through, we press the F8 key.
Basically, each time I press F8, it’s going to run the line that is in yellow.
So, I want to make sure that this actually works -- WORKSHEETS TEMPLATE.COPY -- and, sure enough, when I switch over to Excel, you'll see that we are now in book one.
It created a brand new book with all the data from my template.
Now, in this case, I just have just crazy…just the word data over and over and over.
It'll save it, close it.
Looks good.
Let's go ahead and run that, and you can see on the left-hand side there in the PROJECT explorer it kind of flashes.
Now, we'll try it again.
Let me choose all the remaining departments here.
I’ll press ALT+F8 to see a list of those and I'll click TEST, click RUN, and it actually takes a while to run because it has to go through and create about 60 different workbooks, but what we should see is, I switch over to the folder, the AAA folder, if I refresh this, VIEW, REFRESH, you'll see that it's actually creating workbooks.
There's some more, and so on.
In less than a minute, I will have created 60 copies of the workbook, each with the unique name.
Well, there you have it.
Thanks for stopping by.
We’ll see you next time for another netcast from MrExcel.
