Log In
Register
Home
October 25, 2017
Offline
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:\Users\jose.rossi\Desktop\Excel Files\DCL_CAB INVOICES_BELL\2023 Bell CABS Payments.xlsm")
Set Wb2 = Workbooks.Open("C:\Users\jose.rossi\Desktop\Excel Files\DCL_CAB INVOICES_BELL\X 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,
Trusted Members
October 18, 2018
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
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:\Users\jose.rossi\Desktop\Excel Files\DCL_CAB INVOICES_BELL\2023 Bell CABS Payments.xlsm")
Set Wb2 = Workbooks.Open("C:\Users\jose.rossi\Desktop\Excel Files\DCL_CAB INVOICES_BELL\X 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
1 Guest(s)
