Copy cells and past every 100 cells

[ExcelRoy]

Hi,

I am looking to copy cells B40:B50 and paste them into B202:B212, B364:B374 until B16078:B16088

Would there be some code to achieve this?

Thanks Guys

 

[jim may]

Try...

Sub Foo()
Range("B40:B50").Copy
For i = 202 To 16078 Step 162
    Range("B" & i).PasteSpecial xlPasteValues
Next i
End Sub

 

[ExcelRoy]

Hi Jim,

Many thanks for that. I was kind of hoping to copy formula's aswell?

Is this possible

Thanks

 

[Sanjeev1976]

Hi Jim,

Many thanks for that. I was kind of hoping to copy formula's aswell?

Is this possible

Thanks

Use xlPasteFormulas instead of xlPasteValues

If you don't want the formula to change the results of the copied cells, use formulas with absolute reference i.e. with $ sign

 

[jim may]

Then substitute in:

Range("B" & i).PasteSpecial xlPasteFormulas

 

[ExcelRoy]

Thanks guys