Maintain Focus on current Worksheet?

[gheyman]

I have a userform with a command button that copies a range of formulas and then performs a pastespecial in a worksheet. My problem is, the Userform with the command button is launched from a differnt sheet then where the Copy/Paste takes place. You can see the sheets change while it performs the code and it looks very unprofessional. How do I keep the original worksheet in view while the code is performed?

Here is the code:

Private Sub TextBox42_Change()
'Identifies the current sheet as ws
Set ws = ActiveSheet

'Code
Sheets("HOURS").Activate
Range("AE3:AE761").Select
Selection.Copy
Range("AD3").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

'Returns to current page after code (the current page could be different depending on where the Userform was launched from
ws.Activate
End Sub


HELP!!!! ANY HELP IS VERY APPRECIATED.

 

[BrianB]

As we progress in writing code we do not use Activate/Select unless we actually need to see something on screen. In this case you can replace your copy/pastespecial code (and eliminate the need to activate the sheet) with :--

    ' code
    Worksheets("HOURS").Range("AE3:AE761").Copy
    Worksheets("HOURS").Range("AD3").PasteSpecial _
        Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False