0

My current method for copying A4:M4 into X number of rows is crashing my computer. I am hoping someone might have a VBA loop that is quicker than my current method.

I need to copy A4:M4 into A5:M5,A6:M6....etc until X (rows) is reached) and then copy A5:M5.... etc and paste special back into the same cells. This can be done all in 1 function before moving to the next row.

The X (end of the loop) is defined by cell A2 and is defined by the number of rows in another sheet. My current sheet has 800k rows of data, hence I want to copy a single formula, paste into row, then copy and paste special (so the file size isn't large).

I hope this makes sense.

0
' I need to copy A4:M4
Range("A4:M4").Select
' into A5:M5,A6:M6....etc until X (rows) is reached
Selection.AutoFill Destination:=Range("A4:M" & CStr(4 + X)), Type:=xlFillDefault
' then copy A5:M5
Range("A5:M5").Select
Selection.Copy
' and paste special back into the same cells
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
5
  • Thank you Akina, how do I make the X = a cell reference A2?
    – John
    Jul 5 '19 at 6:01
  • Also, would a loop speed it up by performing all of the functions in one go before moving to the next line? Also this: ' then copy A5:M5 Range("A5:M5").Select Should be: ' then copy A5:M5,A6:M6.......etc. Range("A4:M" & CStr(4 + X)).Select
    – John
    Jul 5 '19 at 6:15
  • how do I make the X = a cell reference A2? Use If(IsNumeric(Range("A2").Value), CInt(Range("A2").Value), 4)... and avoid (or add test for) of this value less than 4. The best way is to test and assign to X before AutoFill statement. would a loop speed it up by performing all of the functions in one go before moving to the next line? 99%.
    – Akina
    Jul 5 '19 at 6:18
  • Hi Akina, any chance you'd be able to re-write it as a loop, as in A5:M5, copy A4:M4, paste into A5:M5, then copy A5:M5 and paste special into A5:M5. (Next) copy A4:M4, paste into A6:M6, then copy A6:M6 and paste special into A6:M6. And loop for the number of iterations in cell A2? (etc. until X rows have been done)
    – John
    Jul 7 '19 at 23:02
  • Actually, never mind, your solution works quicker than a loop. Thanks heaps.
    – John
    Jul 8 '19 at 0:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.