VBA PasteSpecial with multiple windows on workbook changes activesheet in window 1

[CharlieRutledge]

Kind of an obscure issue, but driving me nuts.

When I have multiple windows open to the same workbook, and invoke VBA code that does a paste special, then it changes the ActiveSheet in Window 1 to be the sheet that I pasted to. Can't figure out how to prevent it from happening, or how to undo the effect.

Some simplified code to reproduce:

Sub TestFreeze()
    Const rn = "testrange"

    Range(rn).Copy
    Range(rn).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlPasteSpecialOperationNone, SkipBlanks:=False, Transpose:=False
    
End Sub

Setup:

• 2 open windows to the workbook (via View | New Window)
• worksheet in second window (the one with the "- 2" suffix) has a range named "testrange", and a button that invokes TestFreeze (TestFreeze will copy the values in "testrange" to the row immediately beneath it)
• first window is active to a different worksheet in the workbook (any sheet except the one above)

After invoking TestFreeze from window two, window one switches to show the sheet containing "testrange". Not sure why that would be, but it happens consistently. Any pointers on how to prevent would be appreciated. Not a huge issue, but annoying enough for what I'm working with.

If I can't prevent it from happening, I thought I would try to modify TestFreeze to capture the active sheet in window 1 and restore it after the paste. I can figure out how to do the first part, but can't figure out how to do the second part. Adding this code before the PasteSpecial will capture the active sheet in window 1 at the beginning:

    Dim PreserveFirstSheet As Worksheet
    Dim currWindow As Window
    
    Set PreserveFirstSheet = Nothing
    For Each currWindow In Range(rn).Parent.Parent.Windows ' Loop through the windows in the workbook looking for window 1 (not necessarily the same as .Windows(1))
        If currWindow.WindowNumber = 1 Then
            Set PreserveFirstSheet = currWindow.ActiveSheet
            Exit For
        End If
    Next currWindow

So after that PreserveFirstSheet is the worksheet that I want to be active in window 1. But as far as I can see, there is no function to set the active sheet for a specific window. The ActiveSheet property for the Window object is read only.

Any suggestions for how to either keep the problem from happening in the first place, or to fix it if is does?

Thanks!

 

[JLGWhiz]

Have you trried using Sheets() instead of Windows() to maneuver through your workbook? this link might help

Working with Worksheets using Excel VBA (Explained with Examples)

In this tutorial, I will cover how to work with Worksheets using VBA. I'll also cover a lot of practical examples on using Worksheets. Just like any object in VBA, worksheets have different properties and methods associated with it that you can use while automating your work with VBA in Excel.

trumpexcel.com

Using the Sheets() reference ties it down to the specific sheet whether it is the one displayed on screen or not.

 

[CharlieRutledge]

Thanks for the suggestion. The issue though is that I want to preserve (or reset) the Sheet that actually is displayed on the screen. The paste does the right thing to the right sheet, but then as a side effect it changes the sheet that is displayed in Window 1.

I didn't figure out how to keep the problem from happening, but I did figure out how to restore the original state when it does. If anyone's curious, here's the Sub adding the code that saves and restores:

Sub TestFreeze()
    Const rn = "testrange"

    Dim CurrWindow As Window
    Dim SavedActiveWindow As Window
    Dim FirstWindow As Window
    Dim SavedFirstSheet As Worksheet

    ' Logic to save references to the first window displaying the workbook, the worksheet displayed in that window, and the window that is active when the function is invoked.
    Set SavedActiveWindow = ActiveWindow  ' Active Window when function is invoked
    Set FirstWindow = Nothing
    Set SavedFirstSheet = Nothing
    For Each CurrWindow In ActiveWorkbook.Windows
        If CurrWindow.WindowNumber = 1 Then  ' Indicates the first window displaying one of the active workbook's sheets.  This is the one that gets changed by the paste
            Set FirstWindow = CurrWindow
            Set SavedFirstSheet = CurrWindow.ActiveSheet
            Exit For
        End If
    Next CurrWindow

    ' The code to copy and paste.  The PasteSpecial is the command that has the unwanted side effect of changing the worksheet displayed in window 1
    Range(rn).Copy
    Range(rn).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlPasteSpecialOperationNone, SkipBlanks:=False, Transpose:=False

    ' The code to restore the windows to their original state.
    FirstWindow.Activate  ' First activate window 1
    SavedFirstSheet.Activate  ' Then activate the saved sheet.  This causes the sheet to be displayed in window 1 again
    SavedActiveWindow.Activate  ' And then activate the window that was active when the function was invoked so it doesn't jump around on the user

End Sub

Way more code to fix the side effect than to accomplish the task, but worth it so it stops driving me nuts!