VBA To Copy & Paste A Range After Changing 1 Cell

[anil92]

Hello, I hope all is well.

I have an excel spreadsheet where all I need to do is run the following loop:

(1) Change Cell Sheet1_$G$11 to Sheet2_$Y21 (starts with Y21, then after the loop it should go down one and repeat the process)

(2) Once this is done, sort a table I have from Sheet1_$A$20:$U$15053 by M (ascending so highest # is at top)

(3) Then copy range Sheet1_$G$21:$U$21 (since this will be the highest row once ascending) and paste special (values) this range in Sheet2_$Z21

(4) Start over by changing Cell Sheet1_$G$11 to Sheet2_$Y22 and repeating the process.

Can anyone please help? Thanks!

[#2]

@anil92 

Had a friend send us this but it's unfortunately not working correctly.

Sub test1()
Dim sh1 As Worksheet
Dim sh2 As Worksheet

Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")

' 25 is the numerical form of Y. Below grabs last row in Y
row_amount = sh2.Cells(1000000, 25).End(xlUp).Row

' Loop
For i = 21 To row_amount
' Set G = Y value
sh1.Cells(11, "G") = sh2.Cells(i, "Y")

' Set your sort. key1 is col you want to sort on.
sh1.Range("A20:U15053").Sort key1:=Range("M1"), order1:=xlAscending, Header:=xlYes

' Copy values and paste into sheet2
sh1.Range("G21:U21" & rowsDl).Copy

sh2.Activate

' Paste in these columns where ever row i is.
Range("Z" & i & ":AT" & i).Select
ActiveSheet.Paste

sh1.Activate


Next
End Sub

[#3]

@anil92 Tested your macro and ran into some problems.

Firstly, I would include "sh1.Activate" before you enter the loop, although it's not necessary if you are sure that the macro is always executed from within Sheet1 (i.e. when Sheet1 is already active).

Secondly, the code that sorts Sheet1 suggests that the range to be sorted starts on row 20, including a header line. Then you set "M1" as the sort key. Thus, your sort key is outside the sort range. Change it to "M20" and your macro will run all the way through, although I can not really say if it is doing what you want it to do. That's for you to judge.

Went through the rest of the code and wondered what the purpose is for the term "& rowsDI" in the section where you copy into Sheet 2. Since the variable "rowsDI" never gets set, it is always empty and serves no purpose. And then, in the last bit of code you copy/paste a range G20:U21 (1 row, 15 columns) to a range Zi:ATi (1 row, 21 columns). It works, but you could use "Range("Z" & i).Select" as well.