Copy & Paste multiple cells & rows in VBA excel

Question

I have to copy multiple rows in VBA excel repeatedly.

Does anyone knows some shorter solution how to make this?

My current solution looks like this:

'Select row with sun altitude values
Range("A11:A277").Select
Selection.Copy
Range("EE11:EE277").PasteSpecial xlPasteValues
'Select row 11
Range("B11:EB11").Copy
Range("EF11").PasteSpecial xlPasteValues
Range("EF37").PasteSpecial xlPasteValues
Range("EF41").PasteSpecial xlPasteValues
Range("EF71").PasteSpecial xlPasteValues
Range("EF101").PasteSpecial xlPasteValues
Range("EF131").PasteSpecial xlPasteValues
Range("EF161").PasteSpecial xlPasteValues
Range("EF191").PasteSpecial xlPasteValues
Range("EF221").PasteSpecial xlPasteValues
Range("EF251").PasteSpecial xlPasteValues
'Select column B and paste in every next columns
Range("B11:B277").Select
Selection.Copy
Range("EF11").PasteSpecial xlPasteValues
Range("FU11").PasteSpecial xlPasteValues
Range("FX11").PasteSpecial xlPasteValues
Range("HM11").PasteSpecial xlPasteValues
Range("HQ11").PasteSpecial xlPasteValues
Range("JF11").PasteSpecial xlPasteValues

In general I have to copy 10 the same rows and at least 6 the same columns. The exact distance between every row is 30 interspersed in every 26, as per above and in the picture attached. I have seen previous solution for copy multiple cells in VBA provided, however it refers more for issues with external workbooks.

Thanks & regards,

And what is the issue with your code? Any errors? What is your question? You didn't ask one yet. — Pᴇʜ
– Pᴇʜ, Commented Aug 20, 2018 at 10:38
Hi, I would like to make my code shorter when possible. Do you know any solution how to do this? — Geographos
– Geographos, Commented Aug 20, 2018 at 11:10

Chronocidal · Accepted Answer · 2018-08-20 11:33:12Z

3

By combining the Areas into one Range with Commas. So this:

'Select row 11
Range("B11:EB11").Copy
Range("EF11").PasteSpecial xlPasteValues
Range("EF37").PasteSpecial xlPasteValues
Range("EF41").PasteSpecial xlPasteValues
Range("EF71").PasteSpecial xlPasteValues
Range("EF101").PasteSpecial xlPasteValues
Range("EF131").PasteSpecial xlPasteValues
Range("EF161").PasteSpecial xlPasteValues
Range("EF191").PasteSpecial xlPasteValues
Range("EF221").PasteSpecial xlPasteValues
Range("EF251").PasteSpecial xlPasteValues

becomes

'Select row 11
Range("B11:EB11").Copy
Range("EF11,EF37,EF41,EF71,EF101,EF131,EF161,EF191,EF221,EF251").PasteSpecial xlPasteValues

If the Rows are pasted at a regular interval, you could use a Loop, either to pate, or to build a Range

For i = 1 to 17 Step 4 'Paste every 4 rows
    Cells(136,i).PasteSpecial xlPasteValues 'Paste in column EF, a.k.a. column 136
Next i

or

Set RngTmp = Cells(136,1)
For i = 5 to 17 Step 4 'Paste every 4 rows
    Set RngTmp = Union(RngTmp, Cells(136,i)) 'Add the next cell to the range
Next i
RngTmp.PasteSpecial xlPasteValues 'Paste to all cells in the range

answered Aug 20, 2018 at 11:33

Chronocidal

8,3551 gold badge15 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Geographos Over a year ago

Hi, Could you tell me what means the 136 value? Thanks

Chronocidal Over a year ago

@MariuszKrukar Column 136 is Column EF, in the same way that Column 2 is Column B, Column 26 is Column Z or Column 27 is Column AA - the Worksheet.Cells([row],[column]) property takes numbers for the Row and Column

John F · Accepted Answer · 2018-08-20 11:43:44Z

1

You can do it in one line. For example, your copy/paste of row 11 can be done:

Range("B11:EB11").Copy
Range("EF11, EF37, EF41, EF71, <all the others in your list> , EF251").PasteSpecial xlPasteValues

That is, you list all the cell destinations separated by commas, in quotes.

answered Aug 20, 2018 at 11:43

John F

2761 silver badge6 bronze badges

Collectives™ on Stack Overflow

Copy & Paste multiple cells & rows in VBA excel

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related