VBA Help with a Copy & Paste of Targeted Columns

Hi VBA Gurus,

I have created a subroutine that copies targeted static columns from one worksheet called "Copy Sheet" to the "Master" worksheet. I have two issues with this For LOOP routine:

1). It does not seem to end, I think its looping back to the beginning. I need to know how to stop the routine at the end of the last copy, paste scenario
2). I need a message box that states "Copy and Paste Completed" once the subroutine is completed.

Can anyone help me with the below? Many Thanks! :)


Public Sub CopyColumnsSourceToTarget()
'Worksheets("Master").Activate
'Dim sheetname As String


Dim sourceBook As Workbook
Dim targetBook As Workbook
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim lrow As Integer
Dim i As Integer


'Must have two tabs with data in both the Master and DHL Inbound - DAO All Americas. Maybe message box here to ensure both tabs with data ?


' Set up the sheets



'Get lrow
lrow = Worksheets("Master").Range("C1").End(xlDown).Row



'Code to Copy
For i = 2 To lrow
If Worksheets("Master").Cells(i, 33).Value = "" Then
Worksheets("Master").Cells(i, 33).Value = Worksheets("Copy Sheet").Cells(i, 29).Value
End If

If Worksheets("Master").Cells(i, 38).Value = "" Then
Worksheets("Master").Cells(i, 38).Value = Worksheets("Copy Sheet").Cells(i, 33).Value
End If

If Worksheets("Master").Cells(i, 39).Value = "" Then
Worksheets("Master").Cells(i, 39).Value = Worksheets("Copy Sheet").Cells(i, 34).Value
End If

If Worksheets("Master").Cells(i, 40).Value = "" Then
Worksheets("Master").Cells(i, 40).Value = Worksheets("Copy Sheet").Cells(i, 35).Value
End If

If Worksheets("Master").Cells(i, 45).Value = "" Then
Worksheets("Master").Cells(i, 45).Value = Worksheets("Copy Sheet").Cells(i, 38).Value
End If

If Worksheets("Master").Cells(i, 46).Value = "" Then
Worksheets("Master").Cells(i, 46).Value = Worksheets("Copy Sheet").Cells(i, 39).Value
End If

Next i



' Done - small clean up

End Sub

thanks Jolivanes - Let me give this a try ...! I will let you know!

Hi Jolivanes, BTW, why offset formula? thank you!

Hi Jolivanes - Thanks. Does the offset refer to the Copy Values columns? This is interesting because I have not thought of this approach. Thank you.