Named Range selection and copy in a new Cell

[Sumeluar]

Good day!

I have a simple macro that it copies a named range with entire rows and pastes it right above where the named range is, I would like to modify it to ask in which new cell I want to insert it. Is probably easy but my limited VBA skills lead me to ask for help.

Sub Insert_New_Task()
Range("NewTask").Select
With Selection.EntireRow.Copy
'Maybe here - Popup to to select the cell where to insert the selected range
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.FillDown
End With
End Sub

Regards!

Sumeluar

 

[Sumeluar]

Fiddling a bit more I added a new line of code:

Sub Insert_New_Task()
Range("NewTask").Select
With Selection.EntireRow.Copy
Range("A210").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove '.PasteSpecial xlPasteValues
Selection.FillDown
End With
End Sub

I'm getting closer as the range is copied on row 210, I hope someone guides me through the rest.

Regards!

Sumeluar

 

[jkpieterse]

This should get you going:

Sub Insert_New_Task()
    Dim destination As Range
    On Error Resume Next
    Set destination = Application.InputBox("Please select the destination for the paste", "Destination", "A210", , , , , 8)
    On Error GoTo 0
    If Not destination Is Nothing Then
        Range("NewTask").Select
        With Selection.EntireRow.Copy
            destination.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove    '.PasteSpecial xlPasteValues
            Selection.FillDown
        End With
    End If
End Sub

 

[Sumeluar]

jkpieterse - Thank you for the prompt and accurate reply, doing what I was looking for.

Regards!

Sumeluar