PDA

View Full Version : Solved: help with copy and paste



peter_act
05-27-2009, 08:29 PM
Hello,

A simple task for you, hard for me:

I want to go to a cell (say I6)
Highlight cells I6:W6
Copy these cells
Scroll down 25 rows to cell I31
Highlight cells I31:W31
Paste

Now Highlight cells I31:W31
Copy these cells
Scroll down 25 rows to cell I56
Highlight cells I56:W56
Paste

Repeat 50 times, each time copying the last range of cells you pasted to a range 25 rows beneath it
I've only done it twice in the example above, just to give an example of what I want to do, but I have a 15000 row worksheet and I need a macro.

I've tried "record new macro" but it keeps running the macro and then going back to my original starting point (Cell I6) instead of the last cell I pasted to.

Hope you can understand this

peter_act

mikerickson
05-27-2009, 08:52 PM
The process you describes makes 50 copies of I6:W6, as does thisDim i As Long
With Range("I6:W6")
For i = 1 to 50
.Offset(25*i,0).Value = .Value
Next i
End With

peter_act
05-28-2009, 03:42 PM
Thanks for that,

It almost worked - unfortunately it pastes as "paste special" ->values
instead of copying the formulas from I6:W6 (my fault - I wasn't clear on that in my explanantion)

I'm assuming the code "Value = .Value " is doing this.
Can you advise?

Still, what you gave me was great - I would never have got there

Thanks,

peter_act

mikerickson
05-28-2009, 04:02 PM
Dim i As Long
With Range("I6:W6")
For i = 1 To 50
.Copy Destination:=.Offset(25*i,0)
Next i
End With
Application.CutCopyMode = False

peter_act
05-28-2009, 08:27 PM
Many thanks for that, worked perfectly.

As I said I wouldn't have got there by myself
I've now added it to my little bits of Excel macro code for future reference

Thanks again

peter_act