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:
Setup:
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:
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!
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)
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!