There is no limit to how many sheets you can have in an Excel workbook. Overall, it is limited by the available memory of your system. And it is very common to work with a large number of spreadsheets in a workbook. Usually, creating a new sheet puts the latest one at the end of the workbook. Of course, you can re-arrange them later on to your liking. In this tutorial, we are going to see how we can reverse the order of worksheets in Excel.
Download Practice Workbook
You can download the sample workbook used for the demonstration from the download box below.
3 Easy Ways to Reverse the Order of Worksheets in Excel
You can always manually rearrange a set of worksheets and have them in reverse order. But there are other options in Excel that help the sheets rearrange and in turn, reverse the order of worksheets. Also, there are VBA codes that help you simplify and automate the reversing process. All of these methods are described in their sections. Follow along to see how each works in detail or find the one you need from the table above.
1. Reverse Order of Worksheets by Dragging
Excel provides a way to rearrange worksheets by clicking on the name of the spreadsheet at the bottom of the workbook. Then drag them to the position where you want them to be. This way, you can rearrange them one by one in such a way that the first sheet goes to the end, the second one goes before that, and so on. Therefore, you can reverse the order of worksheets in Excel by manually clicking and dragging.
For an in-depth demonstration with figures, follow these steps.
- First, navigate your mouse cursor to the bottom-left of your Excel workbook.
- Now click and hold on it until the downward-facing arrow appears on top of it.
- Then, without releasing the click, drag the cursor to the end of all the sheets.
- Now release the mouse button and you will have your first sheet moved to the end.
- After that, do the same for Sheet2.
- And drag it to the place between Sheet5 and Sheet1.
- Upon release, Sheet2 will be moved to the new position.
- Similarly, move Sheet3 in between Sheet5 and Sheet2. Then move Sheet4 in between Sheet5 and Sheet3.
As a result, you can see that all of the worksheets in the Excel workbook are in reverse order now.
2. Using Context Menu
Instead of clicking and dragging, there is another option in Excel that helps you move (and/or copy) the sheets in a workbook. You can find such an option in the context menu that appears once you click on the name of the worksheets. You can rearrange the order of the worksheets in Excel in such a method in a definite order and thus can reverse the order of worksheets.
Follow these steps to see how you can use the context menu option to move and reverse the order of worksheets in Excel.
- First, right-click on the name of the worksheet.
- Then select Move or Copy from the context menu.
- Next, in the Move or Copy box that popped up, move the sheet to the end of the Sheet5. Make sure the current workbook is selected. Otherwise, Sheet1 will move to a new workbook.
- After clicking on OK the first sheet will move to the end of all the sheets.
- Similarly, right-click on Sheet2 and select Move or Copy from the context menu.
- But this time, make sure you put it before Sheet1 in the Move or Copy
- After clicking on OK you will have the Sheet2 between Sheet5 and Sheet1.
- In a similar manner, move Sheet3 in between Sheet5 and Sheet2. And then repeat the same process to move Sheet4 in between Sheet5 and Sheet3.
This way you have the reverse of the original order of the worksheets in Excel too.
3. Embedding VBA Code
Although the methods described in the previous two sections work completely fine, the downside of them is they are only feasible for a handy number of worksheets. Even for a relatively smaller larger number of worksheets, these processes are repetitive and daunting. We can get rid of this boredom and easily automate all of the processes with the help of Microsoft Visual Basic for Applications(VBA). Using VBA to reverse the order of worksheets in Excel is also very helpful for a workbook with a large number of worksheets. Here, you don’t need to manually drag or move any of these.
But first, you need the Developer tab to show on your Excel ribbon to run a VBA code. Enable the Developer Tab in Excel if you don’t have it already. Then follow these simple steps to reverse the order of worksheets in Excel.
- First, go to the Developer tab on your ribbon.
- Then select Visual Basic under the Code group.
- As a result, the VBA window will pop up. Next, go to the Insert tab and click on Module from the drop-down menu.
- After that, select the module and write down the following code in it.
Sub Reverse_WorkSheets() Dim Sheets_Count As Integer Sheets_Count = Application.ActiveWorkbook.Worksheets.Count For i = 1 To Sheets_Count - 1 Application.Worksheets(1).Move After:=Application.Worksheets(Sheets_Count - i + 1) Next End Sub
- Now save and close the window.
- Next, go to the Developer tab and select Macros from the Code group.
- Then select the code from the Macro box and click on Run.
Thus this VBA code will reverse the order of worksheets in Excel irrespective of the number of worksheets you have.
These were all the methods you can use to reverse the order of worksheets in Excel. Hope you will be able to confidently reverse the order of worksheets in your Excel workbooks. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know below.
For more guides like this, visit Exceldemy.com.