Geek Answers Handbook

Avoid changing focus when selecting a range - Excel

I have a macro that creates an additional range from one given, depending on some criteria. This subband is then copied to some place where it forms the data read by the chart.

The macro is called every time I click on the counter, as this changes the original range, and therefore it is necessary to calculate a new subrange.

The procedure works fine, although it is probably very primitive (first coding experience with VBA-Excel). Only when I click on the counter to update the data and start the procedure, the "focus" of the sheet moves from the location of the button to the beginning of the range on which the macro works.

I overcome this using Application.Goto at the end of the procedure to return the upper left corner of the window to where the Chart and button are.

However, this means that every time I run a macro, the contents of the window are moved to a range, and then back to where I want it. This is not very elegant, besides the potential cause of motion sickness; -)

I was wondering if I can avoid this and how.

Thanks for any input, the 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
+4
source share
2 answers

May be:

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
+4
source

As I mentioned, you can either avoid pastespecial, as shown in Way1, or if you need values, replace it with the code specified inWay2

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

Method 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

+4
source

More articles:


All Articles