Copy and paste 10 cells below selection

This is my current macro


Sub MACRO2()

Selection.Copy
Range("B22").Select
ActiveSheet.Paste

For Each cell In Selection
With cell
If Application.WorksheetFunction.IsNumber(cell) Then
.Value = .Value * 100
.NumberFormat = "0"
.Interior.Color = RGB(255, 255, 0)
End If
End With
Next cell

End Sub


This pastes a selection to cell B22 and changes the percentages to whole numbers.

Could someone please be kind enough to tell me how, instead of pasting to cell B22, I can get it to paste 10 cells below the current selection (and then carry out the conversion) ?

Thanks very much

Originally Posted by davesexcel

Not sure what you are doing, see if this helps

Please Login or Register  to view this content.

Thanks, this nearly works but changes the formatting of the original data rather than the new copied data (which is what I am after - sorry if not clear).

Originally Posted by davesexcel

Not sure what you are doing, see if this helps

Please Login or Register  to view this content.

Thanks, this nearly works but changes the formatting of the original data rather than the new copied data (which is what I am after - sorry if not clear).