How do I copy a range to a different location update 1

[Dericki]

a b c d e f g
1 10 11 12
2 15 20 25
3 6 8 9
4
.
.
20

How can copy Row 1 to row 20, update cell d1 to g1 and then copy it back to Row 1. I need to do this to all Rows.

I could copy and put it back, but I am having problems when doung the next Row.

 

[Stretchwickster]

>How can copy Row 1 to row 20
The following line of code allows you to copy the specified range:

Range("A1:D20").Copy

I'm not quite sure what you want to do next - can you explain in more detail?


Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[Dericki]

I want to copy row 1 to row 20 make change and copy it back to row 1. Now I want to select say, row 4 to row 20, make change and copy it back to row 4.

 

[TonyJollans]

Why not just update in place in row 1 (or row 4 or wherever)? This sounds like a homework question to me.

 

[Dericki]

I could do that, but I have a list of over 1000 rows. I want to select any rows, Row 20 is linked to other worksheet that perform calculation. And no, this is not a homework question, I am too old for that.

 

[TonyJollans]

Dericki,

My apologies, I have seen (and answered) posts for seemingly complicated ways of doing things, but now I see - and am happy to help another old man

Copying a row is easy, but I'm not quite sure what you are having trouble with - does this help?

Dim R as Integer
For R = 1 to 19
    ActiveSheet.Rows(R).Copy destination:=ActiveSheet.Rows(20)
    ActiveSheet.Range("G20").Formula = "=D20"
    ActiveSheet.Rows(20).Copy destination:=ActiveSheet.Rows(R)
Next

Enjoy,
Tony

 

[Dericki]

Thanks Tony, but not exactly what i am looking for.

This is what I got so far.

Sub CopyRowTo()

Selection.EntireRow.Name = "MyRange"
Selection.EntireRow.Copy
Range("A20&quot.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

End Sub

This will copy any row selected to A20, I will update info,now I want to copy A20 back to the name "My Range" after I make the change. Help Please.

 

[TonyJollans]

Hi Dericki,

You are copying values from your row to row 20. If there any formulas in the original row then they will be overwritten when you copy back. Is this what you want?

If so, then ..

Range("A20").EntireRow.Copy Destination:=Range("MyRange")

.. will do it

Enjoy,
Tony

 

[Dericki]

Thanks Tony, work like a charm:-D