Copy and paste a range based on a cell value.

[watsonla]

I have a range of data in cells k2:m2
I want to paste it into cells f7:h7
But If cell d1 =1 I want to copy and insert the range once.
If D1=2 I want to insert the range twice so it would occupy F7:h7 and F8:h8 and so on.
I need this to continue to whatever the D1 cell value equals.
I need to write this as macro so a command button will recognise D1 and copy the range and paste the range "x" times always starting at F7:h7.
I would be grateful for any assistence on this please

 

[HongRu]

try

Sub sbCopy()
    myTimes = Range("D2")
    Range("F2").CurrentRegion.ClearContents 'Maybe you need this line to clear F2:H2 values.
    Range("K2:M2").Copy
    Range("F2").Resize(myTimes).PasteSpecial
    Application.CutCopyMode = False
    Range("F2").Select
End Sub

 

[watsonla]

try

Sub sbCopy()
    myTimes = Range("D2")
    Range("F2").CurrentRegion.ClearContents 'Maybe you need this line to clear F2:H2 values.
    Range("K2:M2").Copy
    Range("F2").Resize(myTimes).PasteSpecial
    Application.CutCopyMode = False
    Range("F2").Select
End Sub

Thank you for the reply.
This has partially done what I needed. It has given me some clarity, but i still have an issue that I cannot resolve.
Using the code you supllied, it copied over the existing cells in the range F2:H2 and depending on the number in D2, would write over the existing data below F2:H2.

I have tried the following, but i am stuck.
My attempt below only inserts and moves the existing data down 1 line. But I need the exist data in F7:H7 and lower to move down by the same amount as cell "D2" before the data from K2:M2 is inserted multiple times as directed by the cell in D2
The bottom part of the code works perfectly, thank you.

Sub sbCopy()
myTimes = Range("D2")
Range("F7:H7").Select
Selection.Insert Shift:=xlDown


myTimes = Range("D2")
Range("F2").CurrentRegion.ClearContents 'Maybe you need this line to clear F2:H2 values.
Range("K2:M2").Copy
Range("F7").Resize(myTimes).PasteSpecial
Application.CutCopyMode = False
Range("F7").Select

End Sub

 

[HongRu]

try again.

Sub sbCopy()
    myTimes = Range("D2")
    Range("K2:M2").Copy
    If Range("F2") = "" Then
        Range("F2").Resize(myTimes).PasteSpecial
    Else
        Range("F" & Rows.Count).End(xlUp).Offset(1, 0).Resize(myTimes).PasteSpecial
    End If
    Application.CutCopyMode = False
    Range("F2").Select
End Sub

 

[watsonla]

try again.

Sub sbCopy()
    myTimes = Range("D2")
    Range("K2:M2").Copy
    If Range("F2") = "" Then
        Range("F2").Resize(myTimes).PasteSpecial
    Else
        Range("F" & Rows.Count).End(xlUp).Offset(1, 0).Resize(myTimes).PasteSpecial
    End If
    Application.CutCopyMode = False
    Range("F2").Select
End Sub

Thanks again for your reply.
Unfortunately, this posts the data at the end of the list as show in this example.

I need the existing data to move down and the new data to be inserted at the top of the existing Data, Like below.

 

[HongRu]

How about this?

Sub sbCopy()
    myTimes = Range("D2")
    Range("K2:M2").Copy
    If Range("F2") = "" Then
        Range("F2").Resize(myTimes).PasteSpecial
    Else
        Range("F2:H2").Resize(myTimes).Insert Shift:=xlDown
    End If
    Application.CutCopyMode = False
    Range("F2").Select
End Sub

 

[watsonla]

Thank you so much for your help, that worked perfectly and taught me something.