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

CharlieRutledge

New Member
Joined
Mar 18, 2014
Messages
2
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:

VBA Code:
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:
VBA Code:
    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!
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Have you trried using Sheets() instead of Windows() to maneuver through your workbook? this link might help

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

CharlieRutledge

New Member
Joined
Mar 18, 2014
Messages
2
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:

VBA Code:
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!
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,123,518
Messages
5,602,138
Members
414,505
Latest member
quoctrungvu99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top