Copy/PasteSpecial without changing focus?

Hi all,

I've got a bit of VBA code which basically copies the contents of 1 table and pastes the values only (not the formulas) into another table. I have this procedure linked to a button which is on a different part of the worksheet. The problem I am having is that whenever I press the button to run the macro, it does what it's supposed to do but it always changes the focus to the table i.e. the view on the worksheet moves to the table that has just had values pasted into it.

If I change the macro so it's just a normal copy/paste function (so it also copies the formulas) the focus remains on the part of the worksheet with the button. It's only when I change the code to include the pastespecial function that the focus changes. Here is the normal copy/paste code:

Code:

---

Range("table1").Copy
    With ActiveSheet
        .Paste ("SA_Demand_Met")
    End With

---

And here it is with the copy/pastespecial function:

Code:

---

Range("table1").Copy
   
    With Range("SA_Demand_Met")
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With

---

The problem is I don't see why the copy/pastespecial code keeps changing the focus. There is no 'select' function in there so why does it keep moving to the area of operations? Is there anyway I can prevent this?

Cheers
-Rob

Not sure what you mean by focus, but I've used this in the past that may or may not help your situation.

VB Code:

Dim CurrentRange as Range
set CurrentRange = selection() ' save current range
' do stuff
CurrentRange.Select

VBAhack