Forum

Stop screen flicker...
 
Notifications
Clear all

Stop screen flickering

3 Posts
3 Users
0 Reactions
187 Views
(@josros60)
Posts: 47
Trusted Member
Topic starter
 

Hi,

I have the code below but when run screen goes crazy updating the work book, tried difference things seems not to work.

 

here is the code:

Sub Copyfrom_Workbook_Another()
Dim Wb1, Wb2 As Workbook
Dim ws1, ws2 As Worksheet
Dim Row, i, j As Long

'Set Variables
Set Wb1 = Workbooks.Open("C:Usersjose.rossiDesktopExcel FilesDCL_CAB INVOICES_BELL2023 Bell CABS Payments.xlsm")
Set Wb2 = Workbooks.Open("C:Usersjose.rossiDesktopExcel FilesDCL_CAB INVOICES_BELLX CTRX ON SAGE300 IMPORT FILE.xls")
Set ws1 = Wb1.Worksheets("Ctrx ON Apr")
Set ws2 = Wb2.Worksheets("Invoice_Details")
Row = ws1.Range("A27").End(xlDown).Row
j = 2

'Stop Screen Updating
' Application.ScreenUpdating = False
'Application.EnableEvents = False
'Application.DisplayStatusBar = False
'Application.Echo False

'Copy Column A
For i = 27 To Row
ws1.Range("A" & i).Copy
ws2.Activate
ws2.Range("G" & j).Select
ActiveCell.PasteSpecial xlPasteValues
ws1.Activate
j = j + 1
Next i

'Copy Column B
j = 2
For i = 27 To Row
ws1.Range("B" & i).Copy
ws2.Activate
ws2.Range("AI" & j).Select
ActiveCell.PasteSpecial xlPasteValues
ws1.Activate
j = j + 1
Next i
'Copy Column C
j = 2
For i = 27 To Row
ws1.Range("C" & i).Copy
ws2.Activate
ws2.Range("J" & j).Select
ActiveCell.PasteSpecial xlPasteValues
ws1.Activate
j = j + 1
Next i

'Copy Column E
j = 2
For i = 27 To Row
ws1.Range("E" & i).Copy
ws2.Activate
ws2.Range("H" & j).Select
ActiveCell.PasteSpecial xlPasteValues
ws1.Activate
j = j + 1
Next i

'Copy Column B
j = 2
For i = 27 To Row
ws1.Range("B" & i).Copy
ws2.Activate
ws2.Range("AI" & j).Select
ActiveCell.PasteSpecial xlPasteValues
ws1.Activate
j = j + 1
Next i

'Close Wb1
'Wb1.Close

'Cancel Variables
Set Wb1 = Nothing
Set Wb2 = Nothing
Set ws1 = Nothing
Set ws1 = Nothing
Row = 0
i = 0
j = 0

'Restore Screen Updating
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

 

Thank you,

 
Posted : 14/04/2023 3:56 pm
Alan Sidman
(@alansidman)
Posts: 223
Member Moderator
 

These lines of code are made inactive as you have them commented out.  Remove the single quote from the front of each of the lines of code.

 

'Stop Screen Updating
' Application.ScreenUpdating = False
'Application.EnableEvents = False
'Application.DisplayStatusBar = False
'Application.Echo False

 
Posted : 15/04/2023 12:03 am
(@debaser)
Posts: 838
Member Moderator
 

You also don't need separate loops (especially as one of them is a repeat of a previous one):

 

Sub Copyfrom_Workbook_Another()
Dim Wb1, Wb2 As Workbook
Dim ws1, ws2 As Worksheet
Dim Row, i, j As Long

'Stop Screen Updating
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayStatusBar = False
End With
'Set Variables
Set Wb1 = Workbooks.Open("C:Usersjose.rossiDesktopExcel FilesDCL_CAB INVOICES_BELL2023 Bell CABS Payments.xlsm")
Set Wb2 = Workbooks.Open("C:Usersjose.rossiDesktopExcel FilesDCL_CAB INVOICES_BELLX CTRX ON SAGE300 IMPORT FILE.xls")
Set ws1 = Wb1.Worksheets("Ctrx ON Apr")
Set ws2 = Wb2.Worksheets("Invoice_Details")
Row = ws1.Range("A27").End(xlDown).Row
j = 2

'Copy Column A:C and E
For i = 27 To Row
ws2.Range("G" & j).Value = ws1.Range("A" & i).Value
ws2.Range("AI" & j).Value = ws1.Range("B" & i).Value
ws2.Range("J" & j).Value = ws1.Range("C" & i).Value
ws2.Range("H" & j).Value = ws1.Range("E" & i).Value
j = j + 1
Next i

'Close Wb1
Wb1.Close

'Cancel Variables
Set Wb1 = Nothing
Set Wb2 = Nothing
Set ws1 = Nothing
Set ws1 = Nothing
Row = 0
i = 0
j = 0

'Restore Screen Updating
With Application
.EnableEvents = True
.DisplayStatusBar = True
.ScreenUpdating = True
End With

End Sub

 
Posted : 18/04/2023 5:00 am
Share: