Fast Worksheet Copy
July 12, 2017 - by Bill Jelen
Ctrl + drag sheet tabs to make a copy.
Yes, you can right-click any sheet tab and choose Move or Copy to make a copy of your worksheet. But that is the very slow way to copy a worksheet.
The fast way: Hold down the Ctrl key. Drag the worksheet tab to the right.
The downside of this trick is that the new sheet is called January (2) instead of February - but that is the case with the Move or Copy method as well. In either case, double-click the sheet name and type a new name.
Ctrl + drag February to the right to create a sheet for March. Rename February (2) to March.
Select January. Shift + select March to select all worksheets. Hold down Ctrl and drag January to the right to create three more worksheets. Rename the three new sheets.
Select January. Shift + select June. Ctrl + drag January to the right, and you’ve added the final six worksheets for the year. Rename those sheets.
Using this technique, you can quickly come up with 12 copies of the original worksheet.
Illustration: Walter Moore
Watch Video
- Ctrl + drag sheet tabs to make a copy. This episode also covers:
- Right-click tab and choose Move or Copy
- Ctrl + drag worksheet for Mar through June
- Select Jan:June and Ctrl + drag January to make 6 copies
- Vote for my uservoice idea
- Using an Immediate Macro to rename sheets
- Podcast Episode 1497 had FillHandleSheets macro.
- There is some bonus VBA here to rename 12 sheets with month names.
Video Transcript
The MrExcel podcast is sponsored by "MrExcel XL", a book with 40+ Excel tips, plus cartoons, cocktails, tweets and jokes.
Learn Excel from MrExcel podcast, episode 1981 - Fast Worksheet Copy!
If you're liking the tips this week, go ahead and subscribe to the "MrExcel XL" playlist, I'll be podcasting the entire book.
Alright so, yesterday we created this beautiful January report, and now we're at the end of the month, we need to make a copy of this worksheet for February. And I have to tell you, I used to do this, by inserting a new worksheet, copying all the cells, pasting, and then trying to get all the settings right. And then Christine from our Toronto office, this was 15 years ago, my last day job, she taught me right-click, and choose "Move or Copy", I realize it's just off the screen for you, "Move or Copy", and then choose "Create a Copy", and we want to move it before the Recap sheet, like that, alright? And this is, I think, how a lot of people actually go through and create their copies. But there's a much, much faster way.
So here's the tip for today! I'm holding down the CTRL key, I click on February, and I drag to the right. You see that little icon there, the sheet with the + ? That's going to make a copy of February. So there's March! CTRL-drag again, there's April! CTRL-drag again, there's May! CTRL-drag again, there's June! Alright now, I'm not going to rename them all right now, but once I have January through June, I click on January, Shift-click on June, I have to go back to the active sheet January, hold down the CTRL key, and drag that whole thing to the right. Now I'm making copies of all of those, and I've created 12 worksheet copies incredibly quickly.
Alright, it's time for a tiny rant here, if I would type January in a cell, and grab the fill handle and drag, Excel is smart enough to know that after January comes February. It's an idea that I sent into the Excel team 10 years ago. Hey, if I CTRL-drag a sheet called January to the right, why don't you automatically call it February, and February would drag to March, and so on.
Alright, so just this month, August 2016, I went out to Excel.UserVoice.com , and posted a new idea - Fill Handle For Worksheet Copy! If you think that would be a great thing, I'd like you to go up and vote. Here's the short link, mrx.cl/sheetautofill ! You can just click the link down the YouTube description. Go out there, give me a vote, if we get enough votes, then maybe the Excel team will think about doing this.
And to make their life easier, let's go back a few years to podcast episode 1497, where I actually posted a macro that would do this exact same thing! Not just month names, but any custom list in the fill handle. So, the code's already there, you can go back, watch that code, or hey, someone from the Excel team watching this, go check out that code. But the simple fact is, I'm just simply too lazy to go through, and rename all the sheets, especially when we can do it in VBA!
I'm going to press ALT+F11 to get over to VBA, the CTR+G to open the immediate window, and then a little bit of code here: for i = 1 to 12, because I have 12 worksheets, : , that says next line, Worksheets(i).Name = Format(DateSerial(2016 and there's that i , see that i is the month number, ,1), in - full month name, format, next i , I'm going to press Enter after that! Alright, and look, it just works, it just went through and renamed the first 12 sheets. Let's do File, Close and return to Microsoft Excel, and you'll see that I have quickly named those sheets! It'd be nice if we didn't have to switch over to VBA, if that would all be automatic.
Hey look, I'm going to be podcasting every day in August, all 40 tips of the "MrExcel XL" book. Hey, you can just save time, buy the whole book right now, I think it's like 25$, go ahead and click that "i" in the top-right hand corner. And we'll get the book on this way to you!
Alright, Episode Recap: First we talked about making a copy, using right click the tab and choose Move or Copy. But then, CTRL-drag the sheet to create individual sheets. And then selecting January:June, and CTRL-drag January to make six copies. Go out and vote for my user voice idea, or, use the VBA macro, OR, go get that macro from podcast episode 1497, and this will be built into your Excel!
Well hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Title Photo: PatternPictures / pixabay
