Copy and paste a range form one sheet to another.

Option Explicit

Sub sCopy()

Dim rBase As Range, rCopy As Range
Dim wsB As Worksheet, wsF As Worksheet

Set wsB = Sheets("Blad1")
Set wsF = Sheets("Fönstertyp")

With wsF
    Set rBase = .Cells.Find(What:=wsB.Range("F4"), _
        After:=.Range("A1"), _
        LookIn:=xlFormulas, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    Set rCopy = .Range(rBase, rBase.Offset(2).End(xlDown)).Resize(, 2)
    rCopy.Copy wsB.Range("B8")
    Application.CutCopyMode = False
End With

End Sub

Regards, TMS

This is better. It clears the Target range before copying and pasting.

Option Explicit

Sub sCopy()

Dim rBase As Range, rCopy As Range
Dim wsB As Worksheet, wsF As Worksheet

Set wsB = Sheets("Blad1")
Set wsF = Sheets("Fönstertyp")

With wsB
    .Range(Range("B8"), Range("B8").Offset(2).End(xlDown)).Resize(, 2).Clear
End With

With wsF
    Set rBase = .Cells.Find(What:=wsB.Range("F4"), _
        After:=.Range("A1"), _
        LookIn:=xlFormulas, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    Set rCopy = .Range(rBase, rBase.Offset(2).End(xlDown)).Resize(, 2)
    rCopy.Copy wsB.Range("B8")
    Application.CutCopyMode = False
End With

End Sub

Regards, TMS

Hi TMS,

Thank you very much for this answer. Do I copy and paste the code in the alt-f11 sheet 3? And how do I get the values to be shown in sheet "Blad1"? Would it be more simple if you just add it in the workbook I added in my first thread (bok1.xlsx)?

Regards,

You're welcome. Thanks for the rep.

Thanks for your kind words.

See the example workbook.

Regards, TMS