1

Hi i am new to VBA so i apologies in advance if my code looks bad. My problem is, I would like to stop my screen from flickering when i run my macro using a button. However, I have tried multiple codes to solve this but to no avail. I have tried Application.ScreenUpdating = False at the start of my sub and switched back to True before ending the sub but the screen still flickers. Is there any other solution for this? Thanks in advance!

Code:

Sub UpdateData()

Application.ScreenUpdating = False
Debug.Print Application.ScreenUpdating
Application.DisplayStatusBar = False
Application.Calculation = xlManual
Application.EnableEvents = False

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet
Dim EMS As Worksheet
Dim TD As Worksheet
Dim JV1 As Worksheet

Set wb1 = ActiveWorkbook

Workbooks.Open ("HR Headcount Report 2018 Australia SEPTEMBER.XLSX")
Set wb2 = ActiveWorkbook
Set EMS = Sheets("Employee Movement Summary")
EMS.Activate
Range("J19").Copy
wb1.Activate
Range("J34").PasteSpecial xlPasteValues

wb2.Activate
Set TD = Sheets("Turnover Dashboard")
TD.Activate
Range("J44").Copy
wb1.Activate
Range("J2").PasteSpecial xlPasteValues
wb2.Activate
Range("J47").Copy
wb1.Activate
Range("J3").PasteSpecial xlPasteValues

P.S. This is the first half of my code. I suspect that the multiple use of .Activate is causing the problem. If that is the case, how can I rewrite my code to avoid using .Activate?

0m3r
  • 12,286
  • 15
  • 35
  • 71
Lim Hong En
  • 39
  • 4
  • 2
    You can treat `.Activate` in the same way as `.Select` - here is the ["How to avoid using Select in Excel VBA"](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) guide - things like `EMS.Range("J19").Copy` or `wb1.ActiveSheet.Range("J34").PasteSpecial xlPasteValues` – Chronocidal Nov 07 '18 at 08:11
  • 2
    I additionally recommend to avoid `ActiveWorkbook` too by setting the opened workbook to the variable directly: `Set wb2 = Workbooks.Open("HR Headcount Report 2018 Australia SEPTEMBER.XLSX")`. • Also you should specify the workbook for **every** sheet `Set EMS = wb2.Worksheets("Employee Movement Summary")` – Pᴇʜ Nov 07 '18 at 08:59

1 Answers1

1

The code below will guide you somewhat to remove all your Activate statements:

Sub UpdateData()

Application.ScreenUpdating = False
Debug.Print Application.ScreenUpdating
Application.DisplayStatusBar = False
Application.Calculation = xlManual
Application.EnableEvents = False

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet
Dim EMS As Worksheet
Dim TD As Worksheet
Dim JV1 As Worksheet

Set wb1 = ThisWorkbook
Set ws1 = ActiveSheet
'better to refer to the actual sheet name rather than the Active Sheet, amend as required

Set wb2 = Workbooks.Open("HR Headcount Report 2018 Australia SEPTEMBER.XLSX")

Set EMS = wb2.Sheets("Employee Movement Summary")
EMS.Range("J19").Copy
ws1.Range("J34").PasteSpecial xlPasteValues

Set TD = wb2.Sheets("Turnover Dashboard")
TD.Range("J44").Copy
ws1.Range("J2").PasteSpecial xlPasteValues
TD.Range("J47").Copy
ws1.Range("J3").PasteSpecial xlPasteValues
Xabier
  • 7,587
  • 1
  • 8
  • 20
  • Thanks for the help! I have managed to remove all `.Activate` from my sub. However, my screen still flickers when I run my macro with a button as it switches between workbooks. Is there a way to fix this? – Lim Hong En Nov 08 '18 at 01:31
  • @LimHongEn you could make the workbook invisible until you finish copying data over then make it visible again, something like `wb2.Visible = False` – Xabier Nov 08 '18 at 09:02