excel vba copy paste cells without user noticing

Question

I have a macro which copies and pastes a lot of cells in a different worksheet. This works fine, my question is:

Is it possible to make the whole macro run in the background?

As the macro will be used by inexperienced excel-users, I dont want them to see the macro jumping around and copying loads of data which makes the screen go crazy.

Thanks for your answers in advance!

A quick internet search would give you the answer, but for your convenience, to turn off screen updating: Application.ScreenUpdating = False and to turn back on: Application.ScreenUpdating = True — 5202456, Apr 25 '18 at 07:45
My sub procedures don't go *'jumping around and copying loads of data which makes the screen go crazy'*. If your 'macros' do, read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba/28700020). — , Apr 25 '18 at 07:47
If your macro is running and `jumping around`, I would throw up a modal form with text, something along the lines of: **Please wait, processing in progress...**. You don't want people updating a worksheet while its being updated anyway — Zac, Apr 25 '18 at 07:59
I found this one of the best explanations online, it is a function I use in all my macros - https://analystcave.com/excel-improve-vba-performance/ — Lowpar, Apr 25 '18 at 08:03
@5202456 normally I don't have a problem googling stuff, but as english isn't my first language, explaining a problem to the search bar can sometimes prove quite difficult, thank you for your answer! — , Apr 25 '18 at 08:41

score 3 · Accepted Answer · edited Apr 25 '18 at 07:47

3

Use application.screenupdating = false at the start of the macro

then Application.Screenupdating = true at the end, and the screen won't jump around.

Also worth adding Msgbox("The macro has finished!") or something similar at the end so that they know it has done its thing.

edited Apr 25 '18 at 07:47

Kostas K.

8,293
2
22
28

answered Apr 25 '18 at 07:45

CFO

288
1
11

score 2 · Answer 2 · answered Apr 25 '18 at 08:09

I'm guessing your 'macro' looks like this.

Sub Macro1()
    Sheets("Sheet1").Select
    ActiveWindow.SmallScroll Down:=4
    Range("J16").Select
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveWindow.SmallScroll Down:=5
    ActiveWindow.SmallScroll ToRight:=2
    Range("I19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet1").Select
    ActiveWindow.SmallScroll Down:=-72
    Range("A1").Select
End Sub

It would be better if written like this.

Option Explicit

Sub Macro1()
    With Worksheets("Sheet2")
        .Range("I19") = Worksheets("sheet1").Range("J16").Value
    End With
End Sub

Learn more with How to avoid using Select in Excel VBA..

Or `Worksheets("sheet1").Range("J16").Copy Destination:=Worksheets("Sheet2").Range("I19")` if you want to copy formatting, etc, too — Chronocidal, Apr 25 '18 at 08:34

score 1 · Answer 3 · answered Apr 25 '18 at 07:51

You have the following tools at hand to achieve your goals:

Application.ScreenUpdating = False ' turn off screen updating

This will hide any screen updates until you turn it on again. If your application is jumping around with various .Activate etc. you may want to save the initial selection point before

various event triggered Sub

To start something "in the background" you may initiate it

Workbook.Open
Workbook.SheetActivate, Workbook.SheetDeactivate
Worksheet.Activate, Worksheet.Deactivate
' etc.

excel vba copy paste cells without user noticing

3 Answers3