VBA Copy Paste Formula

[Steve Belsch]

Hi VBA Experts,

I was wondering if there is a faster way to copy paste formulas across multiple columns. This what I am doing now and it takes to long. I am just trying to copy and entire row and then paste the formula in the row below it.

For iCol = 21 To 73

Range(Cells(i, 21), Cells(i, 73)).Copy
Range(Cells(i + 1, 21), Cells(i + 1, 73)).PasteSpecial xlPasteFormulas

Next iCol

Thanks for the ideas.

Steve

[大灰狼1976]

Hi Steve!
No need for an iCol loop (I don't know what "i" is, but it doesn't matter)

Range(Cells(i, 21), Cells(i, 73)).Copy
Range(Cells(i + 1, 21), Cells(i + 1, 73)).PasteSpecial xlPasteFormulas
you can also write it like below:

Range(Cells(i + 1, 21), Cells(i + 1, 73)) = Range(Cells(i, 21), Cells(i, 73)).formula

[p45cal]

This sort of thing was mentioned here (msg#4): http://www.vbaexpress.com/forum/showthread.php?66360-VBA-Code-to-add-groupings-to-subtotals&p=397133&viewfull=1#post397133 in the ps.
However, you might be able to paste the formulae in one shot to the whole range (multiple rows at once), depending on the formula, whether it's an array-formula and what kind of range(s) it applies to (especially if there are table references). I'd need to see the actual worksheet and formulae concerened.

[Steve Belsch]

Thank you. This solved the problem.

Regards,
Steve