Selecting all cells in all rows and columns after a certain word

[drummerpolo15]

I would like to select all the values including the one with the word in it. I would like to select all the information in every row and column including the one with the value in it which will be a string and every row/column following that word in that column. in its simplest form i was thinking

If cell value in column A = "test" then select all information in that row and in subsequent rows and columns

I would then like to paste this information into a new sheet.

Thank you all for taking the time to read my question

 

[mumps]

Assuming that the word is in column A and that the word is "Test", try:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    Set rng = Range("A2:A" & LastRow).Find("Test", LookIn:=xlValues, LookAt:=xlWhole)
    If Not rng Is Nothing Then
        Rows(rng.Row & ":" & LastRow).Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    End If
    Application.ScreenUpdating = True
End Sub