CODEWITHSUNDEEP
excelvbaexcel-formulaexcel-2016
Lim Hong En
Lim Hong En

Reputation: 39

Screen flickers even when Application.ScreenUpdating is False

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?

Upvotes: 1

Views: 2393

Answers (1)

Xabier
Xabier

Reputation: 7735

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

Upvotes: 1

Related Questions