# Help with fill down formula not working



## Musto85 (Dec 22, 2022)

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 (Dec 22, 2022)

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 (Dec 22, 2022)

Michael M said:


> Maybe this way
> 
> ```
> Private Sub Worksheet_Activate()
> ...


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 (Dec 23, 2022)

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 (Dec 23, 2022)

```
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 (Dec 23, 2022)

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 (Dec 23, 2022)

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 (Dec 23, 2022)

MARK858 said:


> Maybe try...
> 
> ```
> Private Sub Worksheet_Activate()
> ...


Bingo! thank you very much Mark

Thanks johnny and kvsrinivasamurthy, your variant also work


----------



## MARK858 (Dec 23, 2022)

You're welcome



Musto85 said:


> 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


----------

