Dear all,
Rows in Workbook 1 that has column H, I, J or K filled up are ready for transfer.
I would like to copy and paste rows that are ready for transfer equally into 2 existing worksheets (Alex and IMHD) in Workbook 2.
Each worksheet in Workbook 2 requires different information to be copied over from Workbook 1. I have figured out the copy and paste codes.
However, I am unable to find the code that can split the data - half executes the 1st copy and paste code (CopytoAlex), the other half executes the other copy and paste code (CopytoIM).
Note: Feel free to make any clarifications. Tried my best to explain.
Code
Public Function CouriersTemplate()
Workbooks("Workbook 2.xlsb").Activate
Dim sourceColumn As Range, targetColumn As Range
Dim i As Integer, endrow As Integer, WB As Integer, RB As Integer, FI As Integer, CA As Integer, alexCounter As Integer, IMCounter As Integer
Dim alexDate As Date, deliveryDate As Date
Dim order As String
Application.ScreenUpdating = False
alexDate = courierDate("ALEX")
endrow = Workbooks("Workbook 1.xlsb").Worksheets("Masterlist").Cells(Rows.Count, 1).End(xlUp).Row
alexCounter = Workbooks("Workbook 2.xlsb").Worksheets("ALEX").Cells(Rows.Count, 2).End(xlUp).Row + 1
IMCounter = Workbooks("Workbook 2.xlsb").Worksheets("IMHD").Cells(Rows.Count, 2).End(xlUp).Row + 1
For i = endrow To 4 Step -1
WB = Workbooks("Workbook 1.xlsb").Worksheets("Masterlist").Cells(i, 8)
RB = Workbooks("Workbook 1.xlsb").Worksheets("Masterlist").Cells(i, 9)
FI = Workbooks("Workbook 1.xlsb").Worksheets("Masterlist").Cells(i, 10)
CA = Workbooks("Workbook 1.xlsb").Worksheets("Masterlist").Cells(i, 11)
'Get delivery date
deliveryDate = CDate(Workbooks("Workbook 1.xlsb").Worksheets("Masterlist").Cells(i, 6).Value)
For (RB >= 1 Or WB >= 1 Or FI >= 1 Or CA >= 1) And deliveryDate = alexDate Then
Call CopytoAlex(i, alexCounter, order)
alexCounter = alexCounter + 1
Next (RB >= 1 Or WB >= 1 Or FI >= 1 Or CA >= 1) And deliveryDate = alexDate Then
Call CopytoIM(i, IMCounter, order)
IMCounter = IMCounter + 1
End If
Next i
End Function
Display More