Reduce flickering in copy/paste macro

[AmyK023]

Hi - I'm slowing pulling together a macro that will copy and paste information from 1 to 90+ columns (each column has from 1 to 40+ rows of data) into a single column. From an aesthetic standpoint, I'd like to minimize the amout of screen flicker that occurs as the macro moves from the copy location to the paste location.

I'm guessing (hoping) there's a more efficient/robust way to perform the cut/paste. Current code follows.

'Fill in SOP List
Dim NumRoles As Long
Range("I6").Select
NumRoles = Range(Selection, Selection.End(xlToRight)).Columns.Count 'allows for variable number of columns


'Hide Columns minimize screen flicker
Columns("G:Cy").Select
Selection.EntireColumn.Hidden = True






For x = 1 To NumRoles
Cells(4, 8 + x).Select
If Selection <> 0 Then 'only selects columns that have content
Selection.Offset(3, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Cells(5, 3).Select
'Looks for next empty space in column to start pasting selection
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If

Next

Any help is greatly appreciated

 

[Darren Bartrup]

Try Application.ScreenUpdating = False and Application.ScreenUpdating = True at the start and end of your procedure.

ScreenUpdating doesn't automatically turn on again, so if your code stops halfway you need to put Application.ScreenUpdating = True into the immediate window to get everything moving again.

 

[AmyK023]

Thanks, worked like a charm.