Find a header, copy a certain range, find a marker and paste it there

Question

I have a piece of code which copies a certain range from Sheet1, searches in Sheet4 for a text called "Marker 1" and pastes the copied range from Sheet1 in Sheet4 beginning in the cell of "Marker 1".

Sub FindCopyPasteV1()

    Dim FindM1 As Range
    Dim CopyM1 As Range

    With Worksheets("Sheet1").Range("A:DD")

        Set CopyM1 = Sheets("Sheet1").Range("E6:E32")

    End With

    With Worksheets("Sheet4").Range("A:DD")

        Set FindM1 = .Find(What:="Marker 1", LookAt:=xlWhole, MatchCase:=True, SearchFormat:=False)

        CopyM1.Copy FindM1

    End With

End Sub

Now I want to change the code so that I can use a Range Object (FindH1) for the selection of the cells which I want to copy from Sheet1.

Sub FindCopyPasteV2()

    Dim FindH1 As Range
    Dim FindM1 As Range
    Dim CopyM1 As Range

    With Worksheets("Sheet1").Range("A:DD")

        Set FindH1 = .Find(What:="Header 1", LookAt:=xlWhole, MatchCase:=True, SearchFormat:=False)

        Set CopyM1 = Sheets("Sheet1").Range("FindH1.E32") 'This doesn't work.

         'Instead of Set CopyM1 = Sheets("Sheet1").Range("E6:E32")

    End With

    With Worksheets("Sheet4").Range("A:DD")

        Set FindM1 = .Find(What:="Marker 1", LookAt:=xlWhole, MatchCase:=True, SearchFormat:=False)

        CopyM1.Copy FindM1

    End With

End Sub

Answer 1

You can use Resize to reference a range beginning at the found cell:

Set CopyM1 = FindH1.Resize(howManyRows, howManyCols)