How to stop looping when it returns to the start of the loop

[Iceman63]

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

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

 

[Fluff]

Can you please explain what you mean by

but it continues to loop and overwrites the information on the destination sheet.

Also can the "myname" value be found multiple times on either sheet?

 

[BlueHornet]

I'm going to take a wild guess here (because I've done something similar in the past, and this seemed familiar to me) that you have 13 rows of "header" information on the worksheet, which is why you started your row counter at i = 14. From there you set the loop = total rows in column A:A. (Consider that Rows.Count is going to include those original 13 rows.) So if you start an operation from Row 14 and do it "Rows.Count" times, then the final 13 are probably unwanted. See if adjusting your row count constraint does the trick for you.

Alternatively, you can break out of loops with a GoTo command to force the code to operate from another point when the GoTo constraint or limit is reached. (It's not the best way to do that, but it does work.)

 

[Iceman63]

Thanks..in column A on both sheets is a string, the string can vary from number, text or a combination of both. I put 38 in column A on the Supervisor Fri sheet, with blanks in between. On the shop allocation sheet, column A has 38 assigned to each row starting at row 11. The friday sheet could have upto 7 different shop locations. I was trying to loop through and copy the row on Fri sheet with 38 next to it, but when it runs it loops many times. And yes the value can be found on both sheets many times.

 

[Fluff]

In that case can you please post some sample data from both sheets using the XL2BB add-in and explain what should happen.

 

[Iceman63]

The is the Friday sheet where the data comes from

Book1
	A	B	C	D	E	F
13	Shop Location		Asset ID.	Work Order #	Comments	Work Order Hours
14	38		SCP-151	277674	PM SEMI-ANN. FSS ***	4
15	38		SCP-151	263695	PM 500 HR ***	24
16	38		SCP-138	279393	PM REPAIRS	40
17	38		SCP-194	277625	FULL INSPECTION EVERY 6 MONTHS FIRE SUPPRESSION SYSTEM	4
18	38		P702	280617	250 HOUR PM	8
19	38		JUM-005	280614	PM 500 HOUR BOOM (MECH) ***	24
20			TRK-214	281963	PRE-PM INSPECTION	1
21			TRK-208	281965	PRE-PM INSPECTION	1
22			SCP-172	281122	boom repair	20
23			SCP-172	280400	repairs	8
24			SCP-172	279358	HOIST CYL LEAK, PINS, BUSHING	4
25			SCP-172	277898	See Notes	1
26			SCP-172	275640	OIL LEAK MID SHIP AREA	1
27			SCP-172	275607	BRAKES / LEAK	1
28			SCP-172	274768	Dump cylinder pin & Trans Leak (5450)	1
Supervisor Fri

 

[Iceman63]

This is the destination sheet

Book1
	A	B	C	D	E	F
10						A SHOP
11	38	LTM340	269537		2
12	38	LTM340	269537		2
13	38	LTM340	269537		2
14	38	LTM340	269537		2
15	38	LTM340	269537		2
16	38	LTM340	269537		2
17	38	LTM340	269537		2
18	38	LTM340	269537		2
19	38	LTM340	269537		2
20	38	LTM340	269537		2
22						B SHOP
Shop allocation

 

[Iceman63]

Explanation: There are 7 different shop locations, that could be used on any day of the week for work. (Yes there are 6 more sheets with the rest of the days).
I was trying to loop through column A on the friday sheet and copy the data over to the destination sheet from B,C,D and E. I was just trying out Friday, to see how it worked. So in column A on friday or any other day, there could be 7 different shop locations.

 

[Fluff]

So you want to copy each row once to the allocation sheet?
In your example you have 6 rows with 38 on the Friday sheet, but 10 on the allocation sheet. What should happen with those extra rows?

 

[Iceman63]

Yes..
There are 4 more 38's at the bottom of the sheet, the column is actually 94 rows long, with not all of them used. The extra row's might have another shop location in them..