Quick & Easy-

[alwaysXcel]

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?


'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


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:

[Jacob Hilderbrand]

Try something like this:



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

[Zack Barresse]

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. ..


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

[alwaysXcel]

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