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.
Thanks I was struggling with this but adding the screenupdating to true and then back to false after doing the select worked like a charm!
Yea, that’s the trick 🙂
You just saved me a whole lot of headache my friend! And you’re right, now over a year later I was trying to solve this problem and there STILL is almost nothing on this issue on the web. Thanks a bunch!
I remember that like it was yesterday, small issue and headache like hell.
No problem, appreciate that! 🙂
Thank you so much for this. I had not realized why .Activate was not making the Sheet visible. I thought that something changed at some point, but it was the Screen updating that I changed at some point. Thanks again. Had been trying to figure it out for about a year.
You’re welcome 🙂