Forum Discussion
VBA To Copy & Paste A Range After Changing 1 Cell
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
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.