Copy down formulas when new row is added

[RockEd]

Hello, I have a data entry userform that is supposed to enter data into the sheet, and then copy down formulas from the respective columns. The problem I seem to have is that it is copying down the formula's to roughly 200 further than the "bottom" - is there anything particularly obviously wrong with my code?

Note - I've taken out other code that was inputting the data into the sheet as it isn't relevant

Private Sub writedatatosheet()

    Dim lastrow As Integer

 lastrow = Cells(Rows.Count, "A").End(xlUp).row
 
 With ActiveSheet
 .Range("AD2:AZ2").AutoFill Destination:=.Range("AD2:AZ2" & lastrow)
 End With

End Sub

 

[blbat]

You might have already done this, but, I'd check the obvious first- is there hidden data in those 200 "Empty" Rows? Manually Delete those Rows, save the workbook, Close it, then re-open it and run your code again.

 

[johnnyL]

Perhaps:

 .Range("AD2:AZ2").AutoFill Destination:=.Range("AD2:AZ2" & lastrow)

Should be:

 .Range("AD2:AZ2").AutoFill Destination:=.Range("AD2:AZ" & lastrow)

 

[Fluff]

It's copying down too far, because this .Range("AD2:AZ2" & lastrow) is concatenating the value of lastrow with AZ2, so if lastrow is 100 you will get AZ2100
Try

With ActiveSheet
 .Range("AD2:AZ2").Resize(lastrow-1).FillDown
 End With

 

[RockEd]

Thank you @Fluff ; I've decided to change the code slightly to only copy down columns that contain formulas. I've 'named' each cell (is there a correct way of saying this?) so that if users add columns then the code will still work.

Rather than typing (or copy / pasting) the line of code for each named range - can I create an array here?

I'm quite inexperienced with array's though.

Here is what I will have to copy multiple times:

With ActiveSheet
 .Range("Col_Fee").Offset(1, 0).Resize(lastrow - 1).FillDown
 End With

Or my attempt at building an array but failing (getting compile error: For each control variable must be variant):

Dim myarray() As Variant
Dim x As Long

myarray = Array("Col_Fee", "Col_Guaranteed_Stats") '(and continuing for the rest of the named cells)


For Each x In myarray
With ActiveSheet
 .Range(myarray(x)).Offset(1, 0).Resize(lastrow - 1).FillDown

 End With

Next x

Happy to post this as a new question if you'd prefer...

 

[Fluff]

It needs to be like

For x = 0 To UBound(myarray)
With ActiveSheet
 .Range(myarray(x)).Offset(1, 0).Resize(lastrow - 1).FillDown

 End With