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.
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).
So right before the save&close procedure I put simple last tab selecting.
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.
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.
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.