Hi,

I am working in a spreadsheet that gets updated with new data weekly - and I have some fixed formulas in specific columns that never changes, only when new values are added to the cells from the weekly update (i.e. column A to X).

The columns are Y, Z, AA, AB, AC, AD and AE and they all start from Row 6:

- Y: =WEEKNUM(K6;2)
- Z: =VLOOKUP(S6;Wagendetails!F$2:G$310;2;FALSE)
- AA: =VLOOKUP(I6;Wagendetails!B$2:D$420;2;FALSE)
- AB: =VLOOKUP(I6;Wagendetails!B$2:D$420;3;FALSE)
- AC: =IF(V6=0;AB6;V6+AB6)
- AD: =VLOOKUP(C6;Relationen!A$2:C$550;2;FALSE)
- AE: =VLOOKUP(D6;Relationen!E$2:F$10;2;FALSE)

The codes I have used - without any result - are:

Sub CMRDatenDSc_Button2_Click()

If Not Intersect(Target, Range("A:X")) Is Nothing Then 'This makes the code execute only when a value in column C is changed

Application.EnableEvents = False 'This prevents infinate loop

lastrow = Range("A1048576:X1048576").End(xlUp).Row
Range("Y6:AE6").AutoFill Destination:=Range("Y6:AE" & lastrow), Type:=xlFillDefault
Application.EnableEvents = True
End If
End Sub

Or

Sub CMRDatenDSc_Button2_Click()

Private Sub Worksheet_Change(ByVal Target As Range)
lastrow = Range("A1048576:X1048576").End(xlUp).Row
Application.EnableEvents = False 'This prevents infinate loop
Range("Y6:AE6").AutoFill Destination:=Range("Y6:AE" & lastrow), Type:=xlFillDefault
Application.EnableEvents = True
End Sub

Or

Sub CMRDatenDSc_Button2_Click()

a = Cells(A:X).End(xlUp).Row
b = "Y6:AE6" & a
Selection.AutoFill Destination:=Range(b)

End Sub

To sum up: the weekly date will contain the exact same columns but the number of rows will differ, so I need a code to fill the fixed formulas for the specific columns and down - but only down to the last row with data, which will most likely change weekly.

I would appreciate any inputs to what I am doing wrong.

Thanks.