ScreenUpdating = False

A

Aviles

Board Regular
Joined
Dec 17, 2008
Messages
173
Office Version
  1. 365
Platform
  1. Windows
Hello,
I've been working on a spreadsheet for a while now with no issues. For some reason, the ScreenUpdating = False has stopped working all of a sudden. The code does refer to other workbooks and I've read that sometimes causes the problem. I've tried to read other posts on the same problem but I've ended up more confused. Below is the code I'm currently using. Any help would be greatly appreciated!

Code: 
Option Explicit
Sub Update_PrimeFX_Records()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
 'This section copies exported record from PrimeFX to the FMS Playbook
    ChDir "S:\FMS\FMS - Foreign Exchange\Templates\FX Advice Templates"
    Workbooks.Open Filename:= _
        "S:\FMS\FMS - Foreign Exchange\Templates\FX Advice Templates\fx_data.csv"
 
    Sheets("fx_data").Select
    Range("A1:W1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Playbook.xlsm").Activate
    Sheets("PrimeFX Uplink").Visible = True
    Sheets("PrimeFX Uplink").Select
    Range("A7:W7").Select
    ActiveSheet.Paste
    Application.DisplayAlerts = False
    Application.CutCopyMode = False
 
    'Alligns colums in the 'PrimeFX Uplink' tab
    Range("V8:W8").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        .HorizontalAlignment = xlLeft
    End With
 
    Range("S8:T8").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        .HorizontalAlignment = xlRight
    End With
 
    Range("D8").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With
 
    Range("G8").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With
 
    Range("Q8").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        .HorizontalAlignment = xlRight
    End With
 
    Range("A7").Select
 
    'Closes the fx_data CSV file
    Windows("fx_data.csv").Activate
    Sheets("fx_data").Select
    ActiveWindow.Close
 
    'This section copies the 'Database' Client records from Node Database to Playbook
    ChDir "S:\FMS\FMS - Foreign Exchange\Templates\FX Advice Templates"
    Workbooks.Open Filename:= _
        "S:\FMS\FMS - Foreign Exchange\Templates\Help Sheets\Node Database\Node Database.xlsm"
 
    'Copies the 'Database' tab from Node Database to Playbook
    Sheets("Database").Select
    Range("A1:B1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Playbook.xlsm").Activate
    Sheets("Database").Visible = True
    Sheets("Database").Select
    Range("A1:B1").Select
    ActiveSheet.Paste
    Application.DisplayAlerts = False
    Application.CutCopyMode = False
    Range("C7").Select
    Range("A1").Select
 
    'Copies the 'Reference' tab from Node Database to Playbook
    Windows("Node Database.xlsm").Activate
    Sheets("Reference").Select
    Range("A1:F1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Playbook.xlsm").Activate
    Sheets("Reference").Visible = True
    Sheets("Reference").Select
    Range("A1:F1").Select
    ActiveSheet.Paste
    Application.DisplayAlerts = False
    Application.CutCopyMode = False
    Range("A1").Select
 
    'Closes Node Database file
    Windows("Node Database.xlsm").Activate
    Sheets("Node Database").Select
    ActiveWindow.Close
 
    'Formats the PrimeFX Uplink tab 'V' column into hh:mm
    Windows("Playbook.xlsm").Activate
    Sheets("PrimeFX Uplink").Select
    Range("V8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "hh:mm"
    Range("A7").Select
 
    'Formats the PrimeFX Uplink tab 'W' column into hh:mm
    Windows("Playbook.xlsm").Activate
    Sheets("PrimeFX Uplink").Select
    Range("W8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "hh:mm"
    Range("A7").Select
 
    'Formats the Uplink Lookup tab 'C' column into hh:mm
    Sheets("Uplink Lookup").Visible = True
    Sheets("Uplink Lookup").Select
    Range("C8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "hh:mm"
    Range("A7").Select
 
    'Formats the Uplink Lookup tab 'E' column into hh:mm
    Sheets("Uplink Lookup").Select
    Range("E8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "hh:mm"
    Range("A7").Select
 
    Range("E8").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        .HorizontalAlignment = xlLeft
    End With
 
    'Copies the Portfolio codes and Client names from the 'Database' tab and pastes them in the 'Client Lookup' tab to create the Vlookup field
    Sheets("Database").Visible = True
    Sheets("Database").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Client Lookup").Visible = True
    Sheets("Client Lookup").Select
    Range("B1").Select
    ActiveSheet.Paste
    Application.DisplayAlerts = False
    Application.CutCopyMode = False
    Range("A1").Select
 
    Sheets("Database").Select
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("A1").Select
    Sheets("Client Lookup").Select
    Range("A1").Select
    ActiveSheet.Paste
    Application.DisplayAlerts = False
    Application.CutCopyMode = False
    Range("A1").Select
 
    Sheets("Playbook Home").Select
 
    'Copies 'Uplink Lookup' tab and copies to 'Uplink Lookup 2' tab, sorted by Client/FM
    Sheets("Uplink Lookup").Select
    Range("A1:E1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("A1").Select
    Sheets("Uplink Lookup 2").Visible = True
    Sheets("Uplink Lookup 2").Select
    Range("A1:E1").Select
 
    'Sorts the data
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Uplink Lookup 2").Sort.SortFields.Clear
 
    ActiveWorkbook.Worksheets("Uplink Lookup 2").Sort.SortFields.Add Key:=Range("B1:B1500") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
 
    With ActiveWorkbook.Worksheets("Uplink Lookup 2").Sort
        .SetRange Range("A1:E1500")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
 
    End With
 
    'Add =ISNUMBER formula to column D and F to 'Uplink Lookup 2' tab
    Sheets("Uplink Lookup 2").Select
 
    Range("D1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",IF(ISNUMBER(RC[-1]),""number"",""text""))"
    Selection.AutoFill Destination:=Range("D1:D1500")
    Range("D1:D1500").Select
 
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",IF(OR(RC[-1]=""null"",ISNUMBER(RC[-1])),""number"",IF(ISERROR(FIND(""/"",RC[-1])),""text"",""number"")))"
    Selection.AutoFill Destination:=Range("F1:F1500")
    Range("F1:F1500").Select
 
    Range("A1").Select
 
    'Formats the Uplink Lookup 2 tab 'C' column into hh:mm
    Sheets("Uplink Lookup 2").Select
    Range("C1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "hh:mm"
    Range("A1").Select
 
    'Formats the Uplink Lookup 2 tab 'E' column into hh:mm
    Sheets("Uplink Lookup 2").Select
    Range("E1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "hh:mm"
    Range("A1").Select
 
    'Alligns colums in the PrimeFX Uplink 2 tab
    Range("C1:F1").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        .HorizontalAlignment = xlLeft
    End With
 
    Range("A1").Select
    Sheets("Playbook Home").Select
 
    Application.DisplayAlerts = False
    Application.CutCopyMode = False
 
    'Deletes empty cells from the 'Time' tab
    'Sheets("Time").Select
    '[A:A].AutoFilter Field:=1, Criteria1:="="
    '[A1:A1000].SpecialCells(xlVisible).EntireRow.Delete
    'If [A1] = "" Then [1:1].Delete
    'ActiveSheet.AutoFilterMode = False
    'Range("A1").Select
 
    'Sorts the data from the 'Time' tab
    Sheets("Time").Visible = True
    Sheets("Time").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
 
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Time").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Time").Sort.SortFields.Add Key:=Range("A2:A1000") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Time").Sort
        .SetRange Range("A2:A1000")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
 
    Sheets("Time").Select
    Range("A1").Select
    Range("A5").Select
    Range("A1").Select
 
    'Deletes empty cells from the 'Name' tab
    Sheets("Name").Visible = True
    Sheets("Name").Select
    [A:A].AutoFilter Field:=1, Criteria1:="="
    [A2:A1000].SpecialCells(xlVisible).EntireRow.Delete
    If [A1] = "" Then [1:1].Delete
    ActiveSheet.AutoFilterMode = False
    Range("A1").Select
 
    'Sorts the data from the 'Name' tab
    Sheets("Name").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
 
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Name").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Name").Sort.SortFields.Add Key:=Range("A2:A1000") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Name").Sort
        .SetRange Range("A2:A1000")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
 
    Sheets("Name").Select
    Range("A1").Select
    Range("A5").Select
    Range("A1").Select
 
    Sheets("Playbook Home").Select
 
    'Creates message box to notify if PrimeFX records were copied accross correctly
    If Sheets("PrimeFX Uplink").Range("E8").Value > 0 Then
 
        frmTick.Show
        'MsgBox "PrimeFX records have been updated successfully."
 
        Else
 
        frmErrorFour.Show
        'MsgBox "There are no records available to update." & vbCr & _
        '"Export trades from PrimeFX and try again.", vbOKOnly
        Exit Sub
 
    End If
 
    'Sheets("PrimeFX Uplink").Visible = False
    Sheets("Uplink Lookup").Visible = False
    Sheets("Database").Visible = False
    Sheets("Client Lookup").Visible = False
    Sheets("Uplink Lookup 2").Visible = False
    Sheets("Time").Visible = False
    Sheets("Name").Visible = False
    Sheets("Database").Visible = False
    Sheets("Reference").Visible = False
 
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Maybe the workbooks that you are opening have code assigned to the open or close event, which turns the screenupdating off. If you only want to open them to get data, then you could use

Code: 
Sub Update_PrimeFX_Records()
Application.EnableEvents = False
''' existing code here
Application.EnableEvents = True
End Sub
 
Upvote 0
Thanks for the suggestion pcc, but I figured out what was causing the problem.

One of the workbooks that the code gets information from had some code on the "This Workbook" to ensure that the use enables macros. This is what was causing the issue. I have deleted the code and all works fine.

Thanks for your help.
 
Upvote 0
I am having a problem with Excel 2010. ScreenUpating = True does not seem to work at all.

I set ScreenUpdating to False so the screen does not flash but as I loop through a batch of files, I want to show the use the progress by displaying the result after each file is completed.

Any ideas?
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Dynamic Address by VBA
Replies
8
Views
206
MikeVol
MikeVol
D
  • Solved
VBA If statement to continue
2
Replies
17
Views
651
dinunan
D
D
  • Solved
Trying to copy data from a specific sheet in one workbook to another with VBA
Replies
2
Views
158
druck21
D
J
  • Question Question
Differentiating between clients for a specific recap
Replies
6
Views
123
January1991
J
R
  • Solved
Retype Macro to run properly
2
Replies
11
Views
138
Alex Blakenburg
A

Forum statistics

Threads
1,224,595
Messages
6,179,799
Members
452,943
Latest member
Newbie4296

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top