Autofill selection without formatting

Help please, this is driving me crazy!

I have several instances the rows of my worksheet are grouped by shading or borders, I want to add a column that has a new formula, but I don't want to destroy the formatting.

When I do this manually, I enter the formula in the first cell, right-click on the bottom right corner of that cell, drag, then select "Fill without formatting", and this works perfectly.

So I record this as a macro so I can genericize it:
1. Relative ranging:

Please Login or Register  to view this content.

2. Absolute Ranging:

Please Login or Register  to view this content.

What I want to do is type my formula into the first cell, go to the previous column and hit CTRL-DOWN_ARROW to get to the end of the sheet, go to the next column (my target column), SHIFT-CTRL-UP_ARROW to select the range I want my new formula in, then run my macro to fill the formula to the selection.

You say "why don't I just use the drag method?"... well, when your sheet is thousands of rows long, this is tedious and inaccurate. I should be able to do this on the selection object.

What am I missing here? You can see from all the commented lines that I've every method I can think of, but I always get an error. The last attempt produced "Run-time error '1004': AutoFill method of Range class failed."

Please Login or Register  to view this content.

Thanks for your help, I know I'm missing something basic. I've looked up every reference I can find on AutoFill and Ranges but to no avail.

Try:

Please Login or Register  to view this content.

Ahh... Copy-paste instead of fill.... I'll give that a go. THANKS!

Easy once you know how!

Please Login or Register  to view this content.

Works perfectly, thanks!
And thanks for the very quick replies!

Quick enhancement to allow it to work over multiple columns:

Please Login or Register  to view this content.