From the course: Excel VBA: Managing Files and Data

Move a worksheet within the active workbook - Microsoft Excel Tutorial

From the course: Excel VBA: Managing Files and Data

Start my 1-month free trial

Move a worksheet within the active workbook

- When you work in Excel, you will often want to reposition worksheets within a workbook. In this movie, I will show you how to do that. My sample file is 03 06 Move Within, and it's an Excel macro-enabled workbook that you can find in the chapter three folder of your exercise files collection. This workbook contains three worksheets and we won't be working with any of its data, so I'll press alt F11 to move to the visual basic editor. And here I already have a code module created, with a sub-routine called, move to start. So I'll use the down arrow to move down and here I can enter in the code that will move a worksheet. Let's say that I want to move the March worksheet to the leftmost position on the tab bar. I'll need two lines of code for that. So the first will be sheets, then in parentheses and double quotes March, which is the name of the worksheet we'll be using, period, select. And that command is exactly the same as clicking the sheet tab of a worksheet in the program window. So we have selected the worksheet. Now we need to indicate where to move it. And let's say that I want to move it to the leftmost position, and that would be in position one. So I'll type sheets, and then March, we need to identify it even though it is selected, period, move. Now to tell it where it needs to go. We'll use the before keyword. So before, and then follow that with a colon and an equal sign, because we're setting a parameter value. And we want to move it to the left of the first sheet. So within the sheets collection, that would be the sheet in position one. Right, so alt F11 to go back to the worksheet, and we'll see that March is currently in the rightmost position, alt F11, again, to go back to the visual basic editor, F5. Right, looks like there was some action, so I'll press alt F11 to move back, and you see that March is now in position one. Another thing you can do, is move a worksheet to the last position. To do that you count the number of sheets in the sheets collection, and you move it to the right of whatever that count is. You would use the after keyword. So I'll press alt F11 again, and then in the second line of code that I added, instead of before sheets one, I'll say after, and then colon equal, cause we're setting a parameter, sheets, and then which sheet do we want to put it to the right of? The last one. So that would be sheets dot count. So however many sheets there are, we want to move to the right of the last one. Everything looks good. I'll press F5, and then alt F11, to go back. And we see that March has been moved back. When I've worked in Excel VBA, I haven't used move as much as I have copy. But anytime you want to change the structure or order of the worksheets within a workbook using the move method and telling the worksheet exactly where to go is a very useful skill.

Contents