Copy Sort and Paste VBA LOOP

L

LucyT

New Member
Joined
Jan 3, 2019
Messages
2
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

[TABLE="width: 572"]
<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>[TR]
[TD="width: 95, bgcolor: transparent"]store [/TD]
[TD="width: 95, bgcolor: transparent"]post code [/TD]
[TD="width: 95, bgcolor: transparent"]lat[/TD]
[TD="width: 95, bgcolor: transparent"]long[/TD]
[TD="width: 64, bgcolor: transparent"]store[/TD]
[TD="width: 64, bgcolor: transparent"]lat[/TD]
[TD="width: 64, bgcolor: transparent"]long[/TD]
[TD="width: 64, bgcolor: transparent"]miles 1 [/TD]
[TD="width: 64, bgcolor: transparent"]miles 2 [/TD]
[TD="width: 64, bgcolor: transparent"]miles 3 [/TD]
[/TR]
[TR]
[TD="bgcolor: #DAEEF3"]copy data here[/TD]
[TD="bgcolor: #DAEEF3"]copy data here[/TD]
[TD="bgcolor: #DAEEF3"]copy data here[/TD]
[TD="bgcolor: #DAEEF3"]copy data here[/TD]
[TD="bgcolor: transparent"]example[/TD]
[TD="bgcolor: transparent"]lat[/TD]
[TD="bgcolor: transparent"]long[/TD]
[TD="bgcolor: transparent"]Formual [/TD]
[TD="bgcolor: transparent"]Formula[/TD]
[TD="bgcolor: transparent"]Formula[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]example[/TD]
[TD="bgcolor: transparent"]lat[/TD]
[TD="bgcolor: transparent"]long[/TD]
[TD="bgcolor: transparent"]Formual [/TD]
[TD="bgcolor: transparent"]Formula[/TD]
[TD="bgcolor: transparent"]Formula[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]example[/TD]
[TD="bgcolor: transparent"]lat[/TD]
[TD="bgcolor: transparent"]long[/TD]
[TD="bgcolor: transparent"]Formual [/TD]
[TD="bgcolor: transparent"]Formula[/TD]
[TD="bgcolor: transparent"]Formula[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]example[/TD]
[TD="bgcolor: transparent"]lat[/TD]
[TD="bgcolor: transparent"]long[/TD]
[TD="bgcolor: transparent"]Formual [/TD]
[TD="bgcolor: transparent"]Formula[/TD]
[TD="bgcolor: transparent"]Formula[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]example[/TD]
[TD="bgcolor: transparent"]lat[/TD]
[TD="bgcolor: transparent"]long[/TD]
[TD="bgcolor: transparent"]Formual [/TD]
[TD="bgcolor: transparent"]Formula[/TD]
[TD="bgcolor: transparent"]Formula[/TD]
[/TR]
</tbody>[/TABLE]


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


[TABLE="width: 480"]
<colgroup><col width="64" style="width: 48pt;" span="10"><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]copy data here[/TD]
[TD="width: 64, bgcolor: transparent"]copy data here[/TD]
[TD="width: 64, bgcolor: transparent"]copy data here[/TD]
[TD="width: 64, bgcolor: transparent"]copy data here[/TD]
[TD="width: 64, bgcolor: transparent"]example[/TD]
[TD="width: 64, bgcolor: transparent"]lat[/TD]
[TD="width: 64, bgcolor: transparent"]long[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]example[/TD]
[TD="bgcolor: transparent"]lat[/TD]
[TD="bgcolor: transparent"]long[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]example[/TD]
[TD="bgcolor: transparent"]lat[/TD]
[TD="bgcolor: transparent"]long[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]example[/TD]
[TD="bgcolor: transparent"]lat[/TD]
[TD="bgcolor: transparent"]long[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]example[/TD]
[TD="bgcolor: transparent"]lat[/TD]
[TD="bgcolor: transparent"]long[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]copy data here[/TD]
[TD="bgcolor: transparent"]copy data here[/TD]
[TD="bgcolor: transparent"]copy data here[/TD]
[TD="bgcolor: transparent"]copy data here[/TD]
[TD="bgcolor: transparent"]example[/TD]
[TD="bgcolor: transparent"]lat[/TD]
[TD="bgcolor: transparent"]long[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]example[/TD]
[TD="bgcolor: transparent"]lat[/TD]
[TD="bgcolor: transparent"]long[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]example[/TD]
[TD="bgcolor: transparent"]lat[/TD]
[TD="bgcolor: transparent"]long[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]example[/TD]
[TD="bgcolor: transparent"]lat[/TD]
[TD="bgcolor: transparent"]long[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]example[/TD]
[TD="bgcolor: transparent"]lat[/TD]
[TD="bgcolor: transparent"]long[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]copy data here[/TD]
[TD="bgcolor: transparent"]copy data here[/TD]
[TD="bgcolor: transparent"]copy data here[/TD]
[TD="bgcolor: transparent"]copy data here[/TD]
[TD="bgcolor: transparent"]example[/TD]
[TD="bgcolor: transparent"]lat[/TD]
[TD="bgcolor: transparent"]long[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]example[/TD]
[TD="bgcolor: transparent"]lat[/TD]
[TD="bgcolor: transparent"]long[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]example[/TD]
[TD="bgcolor: transparent"]lat[/TD]
[TD="bgcolor: transparent"]long[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]example[/TD]
[TD="bgcolor: transparent"]lat[/TD]
[TD="bgcolor: transparent"]long[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]example[/TD]
[TD="bgcolor: transparent"]lat[/TD]
[TD="bgcolor: transparent"]long[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]


hope this makes sense thank you :biggrin:
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
How restrict copy data from form to sheet based on five days within current month
Replies
3
Views
112
Ali M
A
J
  • Solved
Speeding up VBA
Replies
9
Views
272
jvoss
J
U
  • Question Question
Excel VBA, Loop, Copy, Loop, Paste, Autofilter, return value, Loop.
Replies
0
Views
139
user29383902
U
L
  • Question Question
VBA question
Replies
6
Views
111
Skyybot
Skyybot
N
  • Question Question
Finding the differences then copy and paste code to a new sheet
Replies
1
Views
667
myall_blues
myall_blues

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top