Help with filldown

[Musto85]

Hey everyone, I have attached the sample workbook, can someone help me identifying why it does not fill down the formula on "Management" sheet until the end of the list on sheet2?
The last 10 or so names are not appearing?


Or if there is any other quicker and easier way to link those cell in the Management sheet with the list on sheet1?
The Management sheet is basically a mirror of that list, however the list on sheet1 changes often and therefore I don't want any REF! appearing on Management sheet and also I want to be able to filter it sometimes so I don't want to just put a formula and fill down until the end otherwise when filtering I will have loads of empty cells at the top.

Thanks in advance

[skywriter]

What's the status of the other thread you started?
Are you still waiting for something?
https://www.excelforum.com/excel-pro...-met-then.html

[Musto85]

Yes that was solved skywriter, thanks for the reminder, marked.

This I haven't been able to...

[skywriter]

The code you wrote says to fill from cell A10 to the number of rows on the other sheet which is 81.
So your code is saying fill down from A10:A81, so it's doing what you are telling it to do.

[Musto85]

Thanks skywriter.
That’s the issue then, because on the Management sheet the first row from where the formula then fills down is A10 and not A2 like on the table of sheet2?

I can’t change that, so I want it to be able to fill down the formula to the last used row on the table on sheet2 instead of stopping on A72.

[jolivanes]

Would the following not do?
Clear Managemet sheet, copy Sheet2 data into Management sheet. If columns in different order, no problem.
If changes on Sheet2 (you called it Sheet1 but I assume that's a slip of the keyboard finger), code will clear Management sheet and copy data from Sheet2 into Management sheet again.
Or am I thinking to simple?

[bebo021999]

This code triggered de-activate event of sheet2.
When select other sheet, it copies current list of sheet 2 then overwrite to sheet Management

Option Explicit 
Private Sub Worksheet_Deactivate() 
Dim lr& 
lr = Cells(Rows.Count, "A").End(xlUp).Row ' last row with data of column A 
Application.ScreenUpdating = False 
With Sheets("Management") 
    .Range("A10:C10000").ClearContents 
    Range("A2:B" & lr).Copy 
    .Range("A10").PasteSpecial Paste:=xlPasteValues 
    Range("D2:D" & lr).Copy 
    .Range("C10").PasteSpecial Paste:=xlPasteValues 
    .Range("A9").Select 
End With 
Application.ScreenUpdating = True 
End Sub  

[skywriter]

Please Login or Register  to view this content.