Help with fill down formula not working

[Musto85]

Hey everyone, please see code below, can someone help me identifying why it does not fill down the formula on the "Management" sheet until the end of the list on sheet2?
The last 8 or so names on the table of sheet2 are not appearing on sheet Management?
I've also uploaded two images... Thanks in advance!

Private Sub Worksheet_Activate()

Application.ScreenUpdating = False

With ThisWorkbook.Sheets("Management")
'.Unprotect ("notnow")

Sheet1.Range("A10").Formula = "=sheet2!A2"
Sheet1.Range("A10:A" & Sheet2.Cells(Rows.Count, 1).End(xlUp).Row).FillDown


'.Protect ("notnow")

Application.ScreenUpdating = True

End With


End Sub

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

 

[Michael M]

Maybe this way

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Dim lr As Long
lr = Sheets("Sheet 2").Cells(Rows.Count, 1).End(xlUp).Row 'change sheet name to suit
'.Unprotect ("notnow")
Sheets("Management").Range("A10:A" & lr).Formula = "=sheet2!A2"
'.Protect ("notnow")
Application.ScreenUpdating = True
End Sub

 

[Musto85]

Maybe this way

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Dim lr As Long
lr = Sheets("Sheet 2").Cells(Rows.Count, 1).End(xlUp).Row 'change sheet name to suit
'.Unprotect ("notnow")
Sheets("Management").Range("A10:A" & lr).Formula = "=sheet2!A2"
'.Protect ("notnow")
Application.ScreenUpdating = True
End Sub

Thanks for the reply however that achieves the exact same result. I think I know what's the issue but I'm still not able to resolve it. The data on sheet2 starts from A2 and ends at A81. The fill down formula on Management sheet starts from A10 and goes until A81 and therefore the formula stops at =sheet2!A73.
The table on sheet2 changes, as data gets added or removed so I can't make the range static.

Is there any way to sort this and having the formula on Management sheet until =sheet2!A81 and therefore to reference the exact same last row of the table on sheet2?

 

[MARK858]

Maybe try...

Private Sub Worksheet_Activate()
    Application.ScreenUpdating = False
    Dim lr As Long

    lr = Sheet2.Range("A2:A" & Sheet2.Range("A" & Rows.Count).End(xlUp).Row).Rows.Count
    'Sheet1.Unprotect ("notnow")
    Sheet1.Range("A10").Resize(lr).Formula = "=sheet2!A2"
    'Sheet1.Protect ("notnow")
    Application.ScreenUpdating = True
End Sub

 

[johnnyL]

Private Sub Worksheet_Activate()
'
    Application.ScreenUpdating = False
'
    With ThisWorkbook.Sheets("Management")
'        .Unprotect ("notnow")
'
        .Range("A10").Resize(Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row - 1).Formula = "=sheet2!A2"
'
'        .Protect ("notnow")
    End With
'
    Application.ScreenUpdating = True
End Sub

 

[kvsrinivasamurthy]

Try
Change these lines

Sheet1.Range("A10").Formula = "=sheet2!A2"
Sheet1.Range("A10:A" & Sheet2.Cells(Rows.Count, 1).End(xlUp).Row).FillDown

By

Sheet1.Range("A10:A" & Sheet2.Cells(Rows.Count, 1).End(xlUp).Row).Formula = "=sheet2!A2"

 

[kvsrinivasamurthy]

If you want copy

Sheet1.Range("A10").Formula = "=sheet2!A2"
Sheet1.Range("A10").Copy Sheet1.Range("A10:A" & Sheet2.Cells(Rows.Count, 1).End(xlUp).Row)

 

[Musto85]

Maybe try...

Private Sub Worksheet_Activate()
    Application.ScreenUpdating = False
    Dim lr As Long

    lr = Sheet2.Range("A2:A" & Sheet2.Range("A" & Rows.Count).End(xlUp).Row).Rows.Count
    'Sheet1.Unprotect ("notnow")
    Sheet1.Range("A10").Resize(lr).Formula = "=sheet2!A2"
    'Sheet1.Protect ("notnow")
    Application.ScreenUpdating = True
End Sub

Bingo! thank you very much Mark

Thanks johnny and kvsrinivasamurthy, your variant also work

 

[MARK858]

You're welcome

Thanks johnny and kvsrinivasamurthy, your variant also work

Are you sure about this, as one of them should have the same issue as you had in post number 3