Copy Sort and Paste VBA LOOP

[LucyT]

Hi All

I was looking for some help please on a Loop i need to do, I'm quite a novice when it comes to VBA and need to learn more use for it for work.

I have built a tool that will show me the closest locations of certain store depending the lat and long, only problem is I can only put 1 location in at once.

all the data will be in a sheet called "enter data" I need a Loop to copy the data from the list one at a time into sheet1 then sort by lowest to highest and then copy the result out and repeat.

The enterdata tab will have a list with data in columns A-D and there can be multiple rows ofdata in the same format. I have just put 10 on the loop for a test but Ideallywould like to be go down as long as it needs



when I have tried the code below it either stops half way through or after in copies the first data into sheet1 on the next loop it will copy the second row of data from the data sheet below it which is not what I want it to do the data from enter data should only be entered into cells A2:D2 in sheet1 (essentially it should just recopy over the old data that was copied and pasted before)

Sub copytest()

For i = 1 To 10
Sheets("enterdata").Range("A2:D2" & i).Copy
Sheets("sheet1").Range("A2:D2").PasteSpecial Paste:=xlPasteValues
Sheets("sheet1").Range("J1", Range("J1").End(xlDown)).Sort Key1:=Range("J1"), Order1:=xlAscending, Header:=xlyes
Sheets("sheet1").Range("A2:J6").Copy
Sheets("output").Range("A2:H6" & i).PasteSpecial Paste:=xlPasteValues
Next i

Application.CutCopyMode = False

End Sub

sheet one would look like the below

store	post code	lat	long	store	lat	long	miles 1	miles 2	miles 3
copy data here	copy data here	copy data here	copy data here	example	lat	long	Formual	Formula	Formula
				example	lat	long	Formual	Formula	Formula
				example	lat	long	Formual	Formula	Formula
				example	lat	long	Formual	Formula	Formula
				example	lat	long	Formual	Formula	Formula

<colgroup><col width="95" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3384;" span="4"><colgroup><col width="64" style="width: 48pt;" span="6"><tbody>
</tbody>

the output i would need to look like the below I don't need the formulas copied out into the output just a standard paste

copy data here	copy data here	copy data here	copy data here	example	lat	long	1	1	1
				example	lat	long	2	2	2
				example	lat	long	3	3	3
				example	lat	long	4	4	4
				example	lat	long	5	5	5
copy data here	copy data here	copy data here	copy data here	example	lat	long	1	1	1
				example	lat	long	2	2	2
				example	lat	long	3	3	3
				example	lat	long	4	4	4
				example	lat	long	5	5	5
copy data here	copy data here	copy data here	copy data here	example	lat	long	1	1	1
				example	lat	long	2	2	2
				example	lat	long	3	3	3
				example	lat	long	4	4	4
				example	lat	long	5	5	5

<colgroup><col width="64" style="width: 48pt;" span="10"><tbody>
</tbody>

hope this makes sense thank you

 

[LucyT]

sorry also when it sorts column J from lowest to highest to must include from the columns E -J as well so the order doesn't get mixed up.