Copy range of cells if A11 to A770 has text

[slohman]

I need a macro that will copy A11770 if text is found in Column A

I need it to find the Sheet that users are working on see Macro Example and copy to another worksheet that will always be called Costings

Sub MoveEstimate_to_Costings()
Dim SHEETNAME As String
SHEETNAME = "Estimate1"
SHEETNAME = InputBox("enter the name of a sheet to use", "sheet name", SHEETNAME)
Dim i As Long
Dim i As Integer


Application.ScreenUpdating = False

ThisWorkbook.Worksheets("Costings").Activate

    Range("A11:P770").Select
    Selection.ClearContents

LR = Sheets(SHEETNAME).Range("CC" & Rows.count).End(xlUp).Row

    For i = 11 To 769
    If Sheets(SHEETNAME).Range("M" & i).Value = "Yes" Then
            Do Until Sheets("Costings").Cells(MyRow, MyCol).Value = "" Or MyRow > 770
                MyRow = MyRow + 1
            Loop
            If MyRow <= 770 Then
                Sheets("Costings").Cells(MyRow, MyCol).Value = Sheets(SHEETNAME).Range("A" & i).Value
                MyRow = MyRow + 1
            Else
                MsgBox "You have ran out of room.  Some entries were not copied"
                    Exit For
                End If
            End If
    Next i

This is what I tried to start with but way to confusing and long.

 

[slohman]

Sorry I had the wrong part of the macro selected

I need it to find text in Column A1:A770 copy that row right up to Column P and then paste it into Worksheet "Costings:

Sub MoveEstimate_to_Costings()
Dim SHEETNAME As String
SHEETNAME = "Estimate1"
SHEETNAME = InputBox("enter the name of a sheet to use", "sheet name", SHEETNAME)
Dim i As Long



Application.ScreenUpdating = False

    Range("A11:P769").Select
    Selection.ClearContents

LR = Sheets(SHEETNAME).Range("A" & Rows.count).End(xlUp).Row

    For i = 11 To 769
    If Sheets(SHEETNAME).Range("A" & i).Value = "Yes" Then
            Do Until Sheets("Costings").Cells(MyRow, MyCol).Value = "" Or MyRow > 770
                MyRow = MyRow + 1
            Loop
            If MyRow <= 770 Then
                Sheets("Costings").Cells(MyRow, MyCol).Value = Sheets(SHEETNAME).Range("A" & i).Value
                MyRow = MyRow + 1
            Else
                MsgBox "You have ran out of room.  Some entries were not copied"
                    Exit For
                End If
            End If
    Next i