Excel 2007 : VBA help

I have some spreadsheets that need updating. I have a macro that can pull the data if I input the range manually but I was hoping to get it to pull based on a cell value as I need this to work for different values.

Cell A15 in MySheet has the value. I want it to generate a range to use later in the macro, MyRange so that if the value of A15 was 4 I would get the range to be "A1:A4"

Any ideas?

Sub Test()
'
'
Dim MySheet As String
Dim MyNum As Integer
Dim MyRange As Range
    MySheet = Range("A1").Value
    MyNum = Range("A15").Value
    MyRange = A1: AMyNum
    
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Sheet1").Select
    Range("A1").Select
    Cells.Find(What:=MySheet, After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
        ActiveCell.Offset(0, -1).Range(MyRange).Select
    Selection.Copy
    Sheets(MySheet).Select
    Range("A17").Select
    ActiveSheet.Paste
    
    ' Notify the user that the process is complete.
   MsgBox "Fund List has been updated"
    
End Sub