Find header copy column to last row

[JJCA99]

Hello all,

Stuck on a code where I need to find key word and from that key word copy all rows below it.
I keep getting Method 'Range' of Oject' _ Global Failed error

Header2 = Kps.Cells(5, 2) 
MtTabs = Cells(Rows.Count, 1).End(xlUp).Row
For q = 6 To MtTabs
MAITName = Kps.Cells(q, 1)
Family = Cells(Rows.Count, 7).End(xlUp).Row



Range(Sheets(MAITName).Cells.Find(what:=Header2).Offset(1, 0), Family).Copy ' My Error is here

Any help would be great!

Thanks,
JJ

 

[DanteAmor]

I did not understand some parts of your code, but I hope that the following example will help you

    Header2 = kps.Cells(5, 2)
    MAITName = kps.Cells(6, 1)
    Set h = Sheets(MAITName)
    Set b = h.Cells.Find(what:=Header2, LookIn:=xlValues, lookat:=xlWhole)
    If Not b Is Nothing Then
        lr = h.Cells(Rows.Count, b.Column).End(xlUp).Row
        h.Range(h.Cells(b.Row, b.Column), h.Cells(lr, b.Column)).Copy
    Else
        MsgBox "Header does not exists"
    End If

 

[JJCA99]

That is it! Thanks Dante appreciate the help. -I was only including row numbers in my range of cells. Thanks again

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.