You have finished working on your Excel file and just putting on final touches. Now, you want to make sure that the next user of your Excel file opens it on exactly the correct position. For example, scroll on all sheets to cell A1. Here is how to do that with 3 simple methods.
- Method 1: Scroll to your cells manually on all sheets
- Method 2: Use a VBA macro to scroll to the same cells on all worksheets
- Method 3: Use Professor Excel Tools for scrolling and selecting to the same cell on all worksheets
Method 1: Scroll to your cells manually on all sheets
As so often, scrolling and selecting a certain manually might be the fastest way – especially when working with just a small number of worksheets.
Here are some tips and tricks to speed it up.
Use keyboard shortcuts
- For example, hold down the Ctrl-key and press the arrow up and left keys until you have reached cell A1 (in case you want to scroll there).
- Navigate through your workbook holding down the Ctrl-key and press Page Down / Page Up on the keyboard.
Just a general advice at this point: With keyboard shortcuts you can save a lot of time in Excel. I am offering a comprehensive guide to Excel keyboard shortcuts: If you are interested in Excel keyboard shortcut, please refer to this page.
Select the same cells on all worksheets with a trick
With this trick, you won’t scroll to the desired cell – but you select it on all worksheets.
- Select all worksheets first:
- Click on the leftmost sheet in your workbook.
- Jump to the last worksheet: Hold down the Ctrl – key and click on the small right-arrow in the left bottom corner.
- Hold down the Shift-key on the keyboard and click on the right-most worksheet.
- Because all worksheets are selected now, you can edit them simultaneously. Just a word of caution: You can easily override the same cells on all worksheets like this.
Type something in your target cell (for example in cell A1).
- Now, you have edited cell A1 on all worksheets. You might want to undo this. Click on Undo or press Ctrl + Z on the keyboard.
Cell A1 is still selected on all worksheets.
Method 2: Use a VBA macro to scroll to the same cells on all worksheets
If your Excel workbook has a larger number of worksheets, it might be worth to look into a VBA solution. The following VBA macro selects a cell and scrolls to it.
Sub scrollToCellOnAllSheets() Dim cellName As String 'Replace A1 with your target cell here: cellName = "A1" For Each tempWorksheet In ActiveWorkbook.Worksheets tempWorksheet.Activate 'Select the cell: ActiveSheet.Range(cellName).Select 'Scroll to the cell ActiveWindow.ScrollRow = Selection.Row ActiveWindow.ScrollColumn = Selection.Column Next End Sub
Here is how to use the VBA-Code. If you need more help of how to use VBA macros, please refer to this article.
- Copy the VBA code from above.
- Open the VBA editor by pressing Alt + F11 on the keyboard.
- Insert a new module: Right-click on the active workbook name on the left.
- Go to “Insert” and click on “Module”.
- Paste the code by pressing Ctrl + V on the keyboard.
- Replace A1 with the cell reference you want to scroll to.
- Click on start in the VBA editor.
Method 3: Use Professor Excel Tools for scrolling and selecting to the same cell on all worksheets
This method is most convenient: Use the Layout Manager of Professor Excel Tools, our Excel add-in.
Just click on “Layout Manager” on the Professor Excel ribbon.
- Type your desired cell into the “Select cell” box.
- You can further select if you want to apply this to just the current worksheet, all selected sheets or the entire workbook.
Click on Start and within a second all your worksheets are scrolled to the same cell.
Furthermore, apply much more professional layout at the same time:
- Hide or show gridlines,
- Switch between Page break preview and Normal view,
- Show or hide formulas,
- Show are hide zeroes,
- Show grouping buttons above or under the details, or
- Apply a consistent zoom level to all sheets.
Try it for free here (download starts right away).