Autofill range

Hi, I'm a noob with VBA coding, I got an idea but it seems as if the syntax is throwing off my code. I appreciate the help.

On sheet 1: I have values from A2:A10 with corresponding values from B2:B10. There are values rows 12 and down.

On sheet 2: I have A1=Sheet1!A2 and B1 =Sheet1!B2

I want a macro to autofill down to the last value that I specify. I have labeled A11 on sheet 1 as 'stop' to identify the location of that cell in order to autofill down to 'stop'. Rows will be added between A2:A10 so the location of 'stop' is not constant.

I'm sure this can be done with the copy function as well. I'm just not sure how to specify my autofill range up to the cell i have named 'stop' on sheet 1.

This is my code so far,


Sub Macro1()
Sheets("Sheet2").Select
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:A6"), Type:=xlFillDefault
Range("A1:A6").Select
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B6"), Type:=xlFillDefault
Range("B1:B6").Select
Range("A1").Select
Sheets("Sheet1").Select
Range("C8").Select
End Sub



I appreciate the help.

I apologize. I will take a look at the rules. A11 simply has the value stop.