Formulae not pasting all the way down to end of range

[Mr2017]

Hi

I've got a spreadsheet that imports new or updates existing using VBA.

I then have a Sub which copies the formula in row 1 from columns BY to CL then pastes them into the cells from BY5 to CL5 until the bottom of the range that the new data has gone up to (see below).

The new data goes from columns A to AC and columns AD to BX are blank (with titles) - in case we'll need to add data to them, at a later date.

My question is this: when I run the Sub below, it copies the formula correctly and pastes the formulae down, but the formula ALWAYS stops at row 1007 regardless of the amount of data that I import. The current data goes up to row 1139.

Has anyone ever experienced this? Any suggestions on why it doesn't paste all the way down to the end of the range?

PS the offset (,90) part of the code is because the columns that need formulae to be added go up to column 90 (from column A).

Thanks in advance.

Sub FillFormulae()
   Range("BY5:CL5").Copy Range("BY1:CL1")
   Range("BY5", Range("A" & Rows.Count).End(xlUp).Offset(, 90)).FillDown
End Sub

 

[Fluff]

Firstly you are copying from row 5 & pasting in row 1, not the other way round

 

[Fluff]

Having corrected the copy section,you code works for me with data going down to row 8826
Check that column A has more than 1007 rows of data.
Also you are filling cols BY:CM, not BY:CL, although that shouldn't make any difference.

 

[Mr2017]

Hi Fluff

Thanks for the prompt response.

I'm copying the formulae from row 1 into row 5, and filling columns BY:CL - column CM isn't needed.

Are you saying that the first part of the code should be

Sub FillFormulae()
Range("BY1:CL1").Copy Range("BY1:CL1")
instead of

Sub FillFormulae()
Range("BY5:CL5").Copy Range("BY1:CL1")
Also, the data in column A goes up to row 1139. Is it possible to use another method to count of the number of rows from column A or column AC then tell the FillFormulae Sub to fill the formulae until the row?

Thanks in advance.

 

[Fluff]

The first line should be

   Range("BY1:CL1").Copy Range("BY5:CL5")

If you have data in A1139 then the formulae should fill down to BY1139

 

[Mr2017]

Oh!! Interesting!!

When I made the simple change you've suggested above the rest of it worked, as intended! And the formulae copied all the way down to row 1139!!

Thanks ever so much!!

You've saved me lots of time that I'd have to spend investigating the problem! Have a great weekend!

 

[Fluff]

Glad it's sorted & thanks for the feedback