Sub CopyInsertPaste()
Dim Sws As Worksheet, Dws As Worksheet
Dim Val1 As Variant, Val2 As Variant
Dim Val1Row As Long, Val2Row As Long, MyRow As Long

Val1 = "AA22"
Val2 = "BB33"

Set Sws = Sheets("Sheet1") '<---Sheet copying from
Set Dws = Sheets("Sheet2") '<---Sheet pasting to
On Error Resume Next
Val1Row = Sws.Range("A:A").Find(What:=Val1, After:=Sws.Range("A" &
Rows.Count), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious,
MatchCase:= _
False, SearchFormat:=False).Row

Val2Row = Sws.Range("A:A").Find(What:=Val2, After:=Sws.Range("A" &
Rows.Count), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious,
MatchCase:= _
False, SearchFormat:=False).Row
On Error GoTo 0
MyRow = Application.Max(Val1Row, Val2Row)
Sws.Range("A5:C" & MyRow).Copy
Dws.Range("A2").Insert Shift:=xlDown
End Sub


The code above will start in row 5 of Sheet1 and scan every row in Column A
for the data AA22 or BB33. When the last row of data AA22 or BB33 is found
the macro will select down to that row and over to Column C, so I am
selecting the range A5:C?(unknown row based on last row of text AA22 or BB33
in Column A). Once the range has been selected from Sheet1 the code copies it
and inserts the copied cells into Sheet2 starting in Row 2.

I would like to change this macro so I dont have to specify the data that
needs to be in Column A. As a result the code will start in row 5 of Sheet1
and scan every row in Column A for any data. When the last row of data in
Column A is found I would like for the macro to select down to that row and
over to Column C, so I am selecting the range A5:C? (unknown row based on
last row of data in Column A). Once the range has been selected from Sheet1
I would like for the code to copy the range and insert the copied cells into
Sheet2 starting in Row2.

In other words the code above is performing the same function I just dont
want to specify what data I want it to look for. I want it to look for any
data in Column A and then perform the same steps it already does. Can you
help me?