Copy and Paste Variable Range

attach please a sample file with data and desired result

Hi, massivemike,

maybe this can give you a hint:

Sub EF944549()
Dim rngStart As Range
Dim rngEnd As Range
Dim wsData As Worksheet
Dim wsTarget As Worksheet

Set wsTarget = Sheets("Sheet2")
Set wsData = Sheets("Sheet1")

Set rngStart = wsData.Columns(1).Find(what:=wsData.Range("G1"), after:=wsData.Range("A1"), lookat:=xlWhole)
If Not rngStart Is Nothing Then
  Set rngEnd = wsData.Columns(1).Find(what:=wsData.Range("G2"), after:=rngStart, lookat:=xlWhole)
    If Not rngEnd Is Nothing Then
      wsTarget.UsedRange.Delete
      wsTarget.Range("E6").Resize(rngEnd.Row - rngStart.Row + 1, 5).Value = _
                wsData.Range(rngStart, rngEnd).Resize(rngEnd.Row - rngStart.Row + 1, 5).Value
    Else
      MsgBox "No data found for " & rngStart.Value, vbInformation, "Exit Sub"
      Exit Sub
    End If
End If

Set wsData = Nothing
Set wsTarget = Nothing
End Sub

Ciao,
Holger