How to scroll to the last Excel tab

You may think that scrolling could be the easiest thing to automate in VBA. Unfortunately, task to scroll to the last Excel tab became one of the toughest things to me to code. Today I’ll explain You, why I had troubles to implement that in complex macro tool.

Story time

I got a request for the macro to scroll the external Excel file to the rightmost tab before closing the file. Sounded like work for 3 minutes, huh? But it wasn’t.

At first, I was concerned about implementation in the code .Select or .Activate. In general, I’m trying to avoid it as much as it is possible (I realized that it is not avoidable 100%, but about that maybe in another post).

Attempts

So right before the save&close procedure I put simple last tab selecting.

wb.Sheets(wb.Sheets.Count).Select

To avoid any problems connected with .Select I surrounded that with error handling procedure.

On Error Resume Next
wb.Sheets(wb.Sheets.Count).Select
On Error GoTo 0

I thought it is everything what is needed, so I started to test it.

Unfortunately, I had no required results! That additional workbook opened on the selected sheet, but the workbook sheet tabs were not scrolled to the chosen, last one.

Before my next attempt I modified the code a little bit.

On Error Resume Next
wb.Activate
wb.Sheets(wb.Sheets.Count).Select
wb.Sheets(wb.Sheets.Count).Activate
On Error GoTo 0

Still no results.

I also tried .ScrollWorkbookTabs method, but it was not working too.

ActiveWindow.ScrollWorkbookTabs position:=xlLast

So, I created another workbook to do only tests for scrolling to the last Excel tab. I wanted to imitate the environment of the main macro as much as it’s possible.

Option Explicit

Sub test()

Dim wb As Workbook

Application.ScreenUpdating = False

Set wb = Workbooks.Open("additional Excel file path")

On Error Resume Next
wb.Activate
wb.Sheets(wb.Sheets.Count).Select
wb.Sheets(wb.Sheets.Count).Activate
On Error GoTo 0

Application.ScreenUpdating = True

End Sub

I tried to do that firstly in ThisWorkbook and then in additional workbook. It turned out, that method works in ThisWorkbook, but not in additional.

I also noticed, that when I was trying my code manually, I mean by going line by line with F8, it was also working! Which by the way irritated me even more in this situation.

The twist

Honestly, after 1.5 day of searching Google and test fails I wanted to stop and quit it, considering this as unnecessary cosmetic modification. I was irritated and frustrated so much, that wanted to give up to this “simple” task.

Just before defeat I realized, that stepping into code (F8) kinda turns on .ScreenUpdating. Following this lead, I decided to turn on screen updating before scrolling and turn it off just after that. And…

…that was it.

Option Explicit

Sub test()

Dim wb As Workbook

Application.ScreenUpdating = False

Set wb = Workbooks.Open("additional Excel file path")

'code...

Application.ScreenUpdating = True
On Error Resume Next
wb.Activate
wb.Sheets(wb.Sheets.Count).Select
wb.Sheets(wb.Sheets.Count).Activate
On Error GoTo 0
Application.ScreenUpdating = False

'code...

Application.ScreenUpdating = True

End Sub

Satisfaction & relief

All I had to do was to turn on screen updating to make it all possible. Now I know, that simple methods like .Select or .Activate can’t really work when You not see them. It sounds logical, but it is not so obvious when You are not using them or rather avoiding them as much as You can.