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,
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
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
