Get cell value and use as vba variable

Hi David,

Try this where the range is in cell A2 (change to suit):

Option Explicit
Sub Macro1()
    
    Dim strMyRange As String
    
    strMyRange = Sheets("School Term").Range("A2").Value

    Sheets("School Term").Range(CStr(strMyRange)).Copy

End Sub

Regards,

Robert

deleted....

What excactly are you trying to achieve? Why do you need the values in a variable?

You can also store the range itself in a range variable.

Option Explicit
Sub Macro1()
    
    Dim MyRange As Range

    MyRange = Sheets("School Term").Range("B4:B201", "B436:B572")
    MyRange .Copy

End Sub

Cell Z1 --> B4:B201, B436:B572

Range(range("Z1").value).Copy Dest

worksheet don't seem to work well

That's odd as it worked for me

If the non adjacent range is entered correctly (as protonLeah has done) into the cell your code references it should work.