Specify range based on cell contents?

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:

Please Login or Register  to view this content.

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

, than when i move the tables up or down one row they stop working.

This is not clear. let us take an example . the data is like this with A1 having column heading and data from A2 down upto A10


h1
20
26
95
29
62
6
38
80
54

now you select a table from A3 to A9 from the above range and you want to sort .
another time you want to sort from A2 to A8. in this case do you want to sort with already sorted data or you want go back to original data and then select and sort.

to sort a selected table in a range of data is first select and sort which can be done manually. if you want a macro why dont use this code statement

Please Login or Register  to view this content.

this appears to be trivial and so I think I have not understood your problem.

Hi.

Can anyone please help.

Here's my code:

Sub transpose()
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveSheet.Paste
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:B6").Select
Application.CutCopyMode = False
Selection.Copy
Selection.End(xlUp).Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Application.CutCopyMode = False
End Sub

It is selecting the same number (6) of cells. I hope that it will select the range depending upon the number of cells with contents.

I have attached a excel screenshot for better understanding.

Please help.

Thank you.