Quick & Easy-

This should be fairly simple, my brain is fried right now and I cant think of the simpler way:
How can I change the following code into a for loop or a loop?

Code:

---

'Ext- Destination Sheet
'Sheet7 - Target Sheet
'range("ExtRNG1")- predefined range in the target sheet with a given name
'range("ExtRNG2")-->("ExtRNG9") - same as above

---

I just need to copy each range and paste it in to the Ext sheet

Code:

---

Sub extvalue()
Sheets("Ext").Select
Range("E15").Select
Sheet7.Range("ExtRNG1").Copy
Selection.PasteSpecial (xlPasteValues)
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Sheet7.Range("ExtRNG2").Copy
Selection.PasteSpecial (xlPasteValues)
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Sheet7.Range("ExtRNG3").Copy
Selection.PasteSpecial (xlPasteValues)
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
end sub

---

Thank you!!!!! :bow:

Try something like this:

Code:

---

For i = 1 To 3
Sheet7.Range("ExtRNG" & i).Copy
    Range("E65536").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
Next i

---

I don't understand why you want to use the Copy method - especially when using PasteValues!? VBA is great for transfering values without using the cumbersome Copy/Paste method. ..

Code:

---

For i = 1 To 3
    Range("E65536").End(xlUp).Offset(1) = Sheet7.Range("ExtRNG" & i).Value
Next i

---

Thank you soo much!! This works for me now!!