Avoiding focus change when selecting range

I have a macro which creates a sub range from one given, depending on some criteria. This sub range is then copied at some location, where it forms the data read by a Chart.

The macro is called each time I click a spinner, as this changes the original range and therefore a new sub range needs to be computed.

The procedure works fine, although it is probably very primitive (first coding experience with VBA-Excel). Only point, when I click the spinner to update data and launch the routine, the 'focus' of the Sheet moves from the location of the button, to the beginning of the range the macro works on.

I overcome this by using Application.Goto at the end of the routine, to bring the upper left corner of the window back to where the Chart and the button are.

However, this means that each time I run the macro, the window contents move to the range and then back to where I want it. That is not really elegant, beside potentially causing some sea-sickness ;-)

I was wondering whether I can avoid that, and how.

thanks for any input, code follows.

Sub test_copy2()
Dim InRange As Range
Dim c As Range, o As Range, t As Range
Dim r As Integer
Set InRange = Cells.Range("M434:ATF434")
Set o = Cells.Range("L434")
For Each c In InRange
If c.Offset(-219, 0).Value = 8448 Then
   Set t = Application.Union(o, c)
   Set o = t
End If
Next c
o.Copy
InRange(1, 1).Offset(2, -1).PasteSpecial (xlPasteValues)
Application.Goto Cells.Range("AF456"), Scroll:=True
End Sub

---

也许：

Sub test_copy2()
Dim InRange As Range
Dim c As Range, o As Range, t As Range
Dim r As Integer
Set InRange = Cells.Range("M434:ATF434")
Set o = Cells.Range("L434")
Application.ScreenUpdating = False
    For Each c In InRange
        If c.Offset(-219, 0).Value = 8448 Then
           Set t = Application.Union(o, c)
           Set o = t
        End If
    Next c
    o.Copy
    InRange(1, 1).Offset(2, -1).PasteSpecial (xlPasteValues)
    Application.Goto Cells.Range("AF456"), Scroll:=True
Application.ScreenUpdating = True
End Sub

---

Like I mentioned you can either avoid pastespecial as shown in Way1 or if you want values then replace it with code shown in Way2

o.Copy
InRange(1, 1).Offset(2, -1).PasteSpecial (xlPasteValues)

Way 1

o.Copy InRange(1, 1).Offset(2, -1)

Way 2

InRange(1, 1).Offset(2, -1).Resize(o.Rows.Count, o.Columns.Count).Value = o.Value

链接地址: http://www.djcxy.com/p/37998.html

上一篇: 主线程不能破解WPF BusyWindow

下一篇: 选择范围时避免焦点改变