Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Returning to last active sheet after a macro automatically runs

1

I have made an excel workbook for my trading , it has lots of sheets and each sheet has a certain purpose.It also has  many macros that I learnt to make for over a year . Anyways I have done the hardest part , and only one simple thing that I can't find the answer anywhere on the net !! simply I have a macro that copies certain cells and paste them on another cell , and there is a timer so that the copy paste macro runs automatically every minute lets say .Problem is that after it runs , it will stay on the last sheet where values are pasted and I dont want that because I will have to go manually and press on the last sheet I was working on .

This is the code :

Sub EnergyON()
Application.OnTime Now + TimeValue("00:01:00"), "EnergyOn"
    Sheets("dde").Select
    Range("B1:B7").Select
    Selection.Copy
    Sheets("currencies").Select
    Range("B3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False       
End Sub

 so as this code runs every minute, it copies cells B1 to B7 from sheet "dde" and pastes them on sheet "currencies" . But meanwhile I was working lets say on Sheet1 or Sheet2 or Sheet3 or even any other sheet , I find myself taken to sheet "currencies" every minute and therefore I have to press on the tab of the last sheet i was working on to get back to my work . Is there any line I can add to this simple macro to make this happen ?? thank you very much

Answer
Discuss

Answers

1
Selected Answer

You don't need to "select" anything. Just copy from one sheet and paste to the other.

Sub EnergyON()
    Application.OnTime Now + TimeValue("00:01:00"), "EnergyOn"
    Worksheets("dde").Range("B1:B7").Copy Destination:=Worksheets("Currencies").Cells(3, "B")
    Application.CutCopyMode = False
End Sub

The above code will copy values as well as number, cell and font formats. If you wish to copy values only, use the following code.

Sub EnergyON()
    Application.OnTime Now + TimeValue("00:01:00"), "EnergyOn"
    Worksheets("dde").Range("B1:B7").Copy
    Worksheets("Currencies").Cells(3, "B").PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False
End Sub

Although it seems like less data are moved, the Copy/PasteSpecial method is less efficient because everything is first copied to the clipboard and then values only copied from the clipboard to the destination. Since your code is running automatically on a timer there is also a chance that its use of the clipboard might coincide with the user's use of the same clipboard (there is only one), leading to unexpected results both for the code and the user.

The code below doesn't use the clipboard and inherently deals only with values. Therefore it should be the fastest of the three.

Sub EnergyON()
    Dim Arr As Variant
    
    Application.OnTime Now + TimeValue("00:01:00"), "EnergyOn"
    Arr = Worksheets("dde").Range("B1:B7").Value
    Worksheets("Currencies").Cells(3, "B").Resize(UBound(Arr), UBound(Arr, 2)).Value = Arr
End Sub
Discuss

Discussion

So many people have this issue! I think I will try to make a simple tutorial explaining this concept as it should save poeple a lot of time.
don (rep: 1989) Nov 20, '17 at 4:27 am
Thank you so much for your reply . But this just pastes it , but my original code is paste special , where is pastes "only values " . can you plz modify it so that it does so ??
bluecolorman (rep: 4) Nov 20, '17 at 5:03 am
I have expanded my answer to include a "Values only" version.
Variatus (rep: 4889) Nov 20, '17 at 5:56 am
And then a second, faster "values only" version.
Variatus (rep: 4889) Nov 20, '17 at 10:55 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login