Dynamic AutoFill Destination Range

Hi all this is my first post i hope i don't make any mistake,

I need help with this Macro, I can't don't know how to configure the Autofill Destination range to be the cells on right column to the cells selected in the previous statement.

Thanks in advance for the help.

Sub Macro1()
' Macro1 Macro
'
    Range("A50").Select
    Selection.End(xlToRight).Select
    Selection.End(xlToRight).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A51").Select
    Selection.End(xlToRight).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.AutoFill Destination:=Range("Q51:R58"), Type:=xlFillDefault
   


End Sub

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

I would find it helpful if you could describe what you want this macro to do. Your code from the macro recorder, and there is usually a better way to code things that what the macro recorder provides.

Hi, I'll try my best.

As you can see its my first Macro. I'm trying to automatize a weekly report and this Macro is used to update a table needed for a chart. Basically i need to perform two actions.

1- Select cells from rows 50-58 in the column where Actual values are (that moves every week) and insert new cells on its left.
2- Selects cells from rows 51-58 in the column where last report values are and autofill the column on the right with its formulas.

I hope this helps for better understanding.

As you can see in the Image:

STATEMENTS EXECUTED IN THE IMAGE
Statement 1 -> I know cell with value="ACTUAL" is in row 50. So I move to cell A50.
Statements 2-3 -> Because I have only 3 values (Star, Actual, End) I can select Actual cel with pressing ctrl+right twice.
Statement 4 -> I can select only cells between rows 50-58 pressing ctrl+sift+down and copy them.
Statement 5 -> I insert cells moving to the right.

STATEMENTS TO EXECUTE
Statement 6-7 -> Because after first 5 statements i know there are blank cells i can reach the las values column by selecting cell A51 and pressing ctrl+right once.
Statement 8 -> I can select only cells between rows 51-58 pressing ctrl+sift+down.
Statement 9 -> I need to Autofill the blank cells with the formulas on the range i currently have selected but i don't know how.

I hope is clearer now, thank you very much.