Specify range based on cell contents?

[hgeek23]

I'm trying to create a macro that copies and sorts a table in 2 different places. I recorded a macro that did the trick at first but have come to the realization that since the ranges i recorded are static, than when i move the tables up or down one row they stop working.

I therefore created 5 boxes which include the needed info to vary the range selection in the macro:
Row Orig. Table Begins
Column Orig Table Begins
Last Row of 1st table
Starting Column of 2nd table
Starting Column of 3rd table

I figured that if i had the data in these cells i could use them as a kind of variable range selection method that the macro would pull off of, but i'm unsure of how to incorporate them.

This is the code with the static ranges:

Sub Sort_Returns()

    Range("AN8:AW8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    
    Range("AY8:BH8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    
    Range("AC8:AL8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-36
    Range("AN8").Select
    ActiveSheet.Paste
    
    Range("AY8").Select
    ActiveSheet.Paste
    
    Range("AN8:AO8").Select
    
    With [Months_Indv_returns_BIF]
.Sort key1:=.Columns(2), Header:=xlNo
End With
    

    Range("AP8:AW8").Select
    Range(Selection, Selection.End(xlDown)).Select
Dim Cols As Range, i As Integer
Set Cols = Selection
For i = 1 To Cols.Columns.Count
Cols.Columns(i).Sort key1:=Cols.Columns(i).Cells(1, 1), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Next i


    With [Months_Pegged]
.Sort key1:=.Columns(2), Header:=xlNo
End With


End Sub

Any ideas on how to make it possible to resize the arrays/ranges when something shifts?

 

[PCL]

For a quick explanation have a look on next code
To select the range using cell (1,1) or A1

Sub Macro1()
    Range("A" & Cells(1, 1)).Select

To use the column value store in cel(2,2) and transform it in a letter

Option Explicit
Sub Macro1()
Dim AAA
    AAA = Cells(1, Cells(2, 2)).Address
    AAA = Left(AAA, Len(AAA) - 2)
    AAA = Right(AAA, Len(AAA) - 1)
    Range(AAA & Cells(1, 1)).Select
End Sub