hi folks,
I wrote the below code to loop through a column on a sheet and copy the row information and paste it on the destination sheet. When I run the code, it loops and pastes the information on the destination sheet, but it continues to loop and overwrites the information on the destination sheet. How do I stop this, and tell the code to stop when it is done? Thanks
I wrote the below code to loop through a column on a sheet and copy the row information and paste it on the destination sheet. When I run the code, it loops and pastes the information on the destination sheet, but it continues to loop and overwrites the information on the destination sheet. How do I stop this, and tell the code to stop when it is done? Thanks
VBA Code:
Option Explicit
Sub Transfer()
Dim i As Long, j As Long, lastrow1 As Long, lastrow2 As Long
Dim myname As String
lastrow1 = Sheets("Supervisor Fri").Range("A" & Rows.Count).End(xlUp).Row
For i = 14 To lastrow1
myname = Sheets("Supervisor Fri").Cells(i, "a").Value
Sheets("Shop allocation").Activate
lastrow2 = Sheets("Shop allocation").Range("a" & Rows.Count).End(xlUp).Row
For j = 11 To lastrow2
If Sheets("Shop allocation").Cells(j, "A").Value = myname Then
Sheets("Supervisor Fri").Activate
Sheets("Supervisor Fri").Range(Cells(i, "c"), Cells(i, "f")).Copy
Sheets("Shop allocation").Activate
Sheets("Shop allocation").Range(Cells(j, "b"), Cells(j, "E")).Select
ActiveSheet.Paste
End If
Next j
Application.CutCopyMode = False
Next i
Sheets("Supervisor Fri").Activate
Sheets("Supervisor Fri").Range("A14").Select
End Sub