Copy down formula using VBA

[Mr2017]

Hi

I'm trying to copy a formula from cell B1 and paste it into a fixed range of cells (B7:B11).

But it's not working, as expected?

I used this

Range("b1:b1").copy
Range("b7:b11").FillDown

But it didn't work.

Any ideas why?

TIA

 

[njimack]

Range("B1").Copy Range("B7:B11")

 

[Mr2017]

Great - thanks njimack !

 

[Mr2017]

By the way, I'd like to copy the formula only (without the format), then past that to the range.

I tried this, but it didn't work, as expected. Do you know why?

Range("B1").Copy Range("B7:B11").xlPasteFormulas

TIA

 

[njimack]

That won't work, but either of these will:

Range("B1").Copy
Range("B7:B11").PasteSpecial xlPasteFormulas

Range("B7:B11").Formula = Range("B1").Formula

 

[Mr2017]

Great - thanks!

I'd tried the first option before, with all the code on one row ie

Range("B1").Copy
Range("B7:B11").PasteSpecial xlPasteFormulas

But it didn't work. It gave me a Compile Error that said "Expected: end of Statement"

Not sure why moving the second half of the code to a new line makes it work?

But thank you for your help!

 

[MARK858]

You can only have it on the same line when using destination and not paste or pastespecial so

Range("B1").Copy Range("B7")

or

Range("B1").Copy Destination:= Range("B7")

would work (the first code is shorthand for the second code)
but

Range("B1").Copy Range("B7").PasteSpecial xlPasteFormulas

wouldn't work, it would need to be

Range("B1").Copy 
Range("B7").PasteSpecial xlPasteFormulas

 

[Mr2017]

Ok, great - thanks for clarifying that!