Loop to copy/paste next row using VBA

[bpg_23]

I have the following code from a recorded macro and I'm trying to copy/paste down approximately 1000 rows.

Range("J3:K3").Select
Selection.Copy
Range("N1").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("L3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

My code needs to change the "J3:K3" range and "L3" range to the next row down the list of approximately 1000 rows. Apologize for the basic question, I'm very new to VBA and have spent the last hour or so searching to no avail.

Thanks!

 

[CodeNinja]

Hi bpg,
You can copy a range including the first and last row you wish to copy... So, copying J3:K1000 would copy the entire range. When you paste, it would then paste the way you want... Something like the following should get you close... change the 1000 to whatever number you want...

Range("J3:K1000").Select

Selection.Copy

Range("N1:N1000").Select

Selection.PasteSpecial Paste:=xlPasteValues

Range("L3").Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues

 

[bpg_23]

Hi bpg,
You can copy a range including the first and last row you wish to copy... So, copying J3:K1000 would copy the entire range. When you paste, it would then paste the way you want... Something like the following should get you close... change the 1000 to whatever number you want...

Range("J3:K1000").Select

Selection.Copy

Range("N1:N1000").Select

Selection.PasteSpecial Paste:=xlPasteValues

Range("L3").Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues

Thanks for the tip. That didn't quite do what I needed it to do because a formula is being driven from cells N1:O1.

The values from J3:K3 are being pasted into N1:O1 which is driving a formula that is changing L3. I'm pasting values in L3 so it does not change as the macro works through the rows.

 

[Joe4]

Quite frankly, I find your question a little confusing. It might be more helpful if we could see examples of what your data looks like, and what you want to happen.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[bpg_23]

Quite frankly, I find your question a little confusing. It might be more helpful if we could see examples of what your data looks like, and what you want to happen.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

First time using this so hopefully it works.

Interstate_Exit-US-AL-Alabama.csv
	A	B	C	D	E	F	G	H	I	J	K	L	M
1												33.47505	-81.9954
2	Lat	Long	DIST				Store	Lat	Long
3	32.86002	-85.1732	188.629				AUGUSTA_00105	33.47505	-81.9954	3.740044
4	33.67579	-85.3426	193.1756				BOWLINGGREEN_00601	36.97918	-86.4541	3.740044
5	33.34295	-87.0273	290.3372				MARTINEZ_70701	33.53931	-82.0816	3.740044
6	33.99587	-84.5589	151.6194				GREENVILLE_00109	34.83888	-82.3561	3.740044
7	34.71405	-85.5536	220.8388				CLARKESVILLE_00301	34.60186	-83.5178	3.740044
8	34.99227	-86.8791	297.9714				AUGUSTA_70702	33.42319	-82.0202	3.740044
9	34.98587	-85.2012	210.777				GREENVILLE_00164	34.88826	-82.406	3.740044
10	32.36722	-86.3219	262.3207				AIKEN_00166	33.52701	-81.7222	3.740044
11	32.85449	-85.1786	189.029				BOWLINGGREEN_00201	36.95087	-86.4368	3.740044
Interstate_Exit-US-AL-Alabama

 

[bpg_23]

The previous mini-sheet is not correct -- this one is accurate for the VBA originally posted --

Interstate_Exit-US-AL-Alabama.csv
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1														33.47505	-81.9954
2			Lat	Long	DIST				Store	Lat	Long
3			32.86002	-85.1732	188.629				AUGUSTA_00105	33.47505	-81.9954	3.740044
4			33.67579	-85.3426	193.1756				BOWLINGGREEN_00601	36.97918	-86.4541	3.740044
5			33.34295	-87.0273	290.3372				MARTINEZ_70701	33.53931	-82.0816	3.740044
6			33.99587	-84.5589	151.6194				GREENVILLE_00109	34.83888	-82.3561	3.740044
7			34.71405	-85.5536	220.8388				CLARKESVILLE_00301	34.60186	-83.5178	3.740044
8			34.99227	-86.8791	297.9714				AUGUSTA_70702	33.42319	-82.0202	3.740044
Interstate_Exit-US-AL-Alabama

 

[Joe4]

OK, so is that what your data looks like initially?
Then what do you want to happen after that?
What should it look like when it is done?

 

[bpg_23]

OK, so is that what your data looks like initially?
Then what do you want to happen after that?
What should it look like when it is done?

The lat/long from Cols J/K should be pasted into N1:O1. This will update the distance in Col E.
Col L uses formulas to find the MIN value from Col E. The end of the loop will have the MIN value from Col E pasted as a value in L3.

Sorry for the confusion and thanks for your help.

 

[Joe4]

So, you just want to loop through columns J and K and paste the values into N1:O1?
Then something like this should get you started.

Sub MyCopy()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column J with data
    lr = Cells(Rows.Count, "J").End(xlUp).Row
    
'   Loop through all rows and paste values from J and K into N1:O1
    For r = 3 To lr
        Range(Cells(r, "J"), Cells(r, "K")).Copy Range("N1")
    Next r
        
    Application.ScreenUpdating = True
    
End Sub

I am sure that you will have other stuff to add to the loop, otherwise it just keeps going through all the rows and pasting over the values in N1:O1 without doing anything else.

 

[bpg_23]

So, you just want to loop through columns J and K and paste the values into N1:O1?
Then something like this should get you started.

Sub MyCopy()

    Dim lr As Long
    Dim r As Long
   
    Application.ScreenUpdating = False
   
'   Find last row in column J with data
    lr = Cells(Rows.Count, "J").End(xlUp).Row
   
'   Loop through all rows and paste values from J and K into N1:O1
    For r = 3 To lr
        Range(Cells(r, "J"), Cells(r, "K")).Copy Range("N1")
    Next r
       
    Application.ScreenUpdating = True
   
End Sub

I am sure that you will have other stuff to add to the loop, otherwise it just keeps going through all the rows and pasting over the values in N1:O1 without doing anything else.

Thanks Joe4, I’ll give this a shot and will let you know how it works.