# Named Range and Indirect Formula Using Variables in Cell



## helpexcel (Dec 20, 2022)

Hi - I'm looking for a formula that will look at the values in Sheet1("B3") & Sheet1("D3") to determine the starting cell to copy from Sheet2.  So if B3="F" and D3="5" the starting point would be Sheet2("F5").  I then want the formula to find the last row.  Can this be then be used as a named range that can be pasted to Sheet3("A3")?


----------



## helpexcel (Dec 20, 2022)

tried using code, because I'm not sure it can be done with just formulas, but my code doesn't work either.

```
Sub Copy_Columns()

Set ws2 = Sheet2
Set ws1 = Sheet1
Set ws3 = Sheet3

Dim rowBD, colBD, rng As Range
    Set colBD = ws2.Range("B3")
    Set rowBD = ws2.Range("D3")

Dim LastRow As Long
With ws1
    LastRow = .Cells(.Rows.Count, colBD).End(xlUp).Row
    Set rng = .Range(.Cells(rowBD, colBD), .Cells(LastRow, colBD))
    rng.Copy ws3.Range("A3")
End With

End Sub
```


----------



## helpexcel (Dec 20, 2022)

This fixed it.

```
Dim colBD As Variant
```


----------

