Copy then Paste at User Selected Cell

[norts55]

I am trying to write a macro that selects a named range of cells, copies them to the clipboard, asks the user to pick a cell for pasting and after the selection is made, then paste in the selected cell.

This may be redundant below but I will show the steps more in detail.

Step one: Select name range "_0_a_a_Combo_Box_Footer"
Step two: Copy name range "_0_a_a_Combo_Box_Footer" to Clipboard
Step three: A dialog box pops up asking the user to select a cell to paste the clipboard contents.
Step four: Paste contents of clipboard in selected cell.

I cannot figure out why this macro does not work. Any help would be greatly appreciated as I am very frustrated.
Thanks in Advance to anyone who is willing to help.

Just an FYI, the dialog portion of my code is copied from another thread. If whoever created that reads this, I say thank you.

Sub a_0_0_Paste_Footer()'
' a_0_0_Paste_Footer Macro
'


'
   
    Application.Goto Reference:="_0_a_a_Combo_Box_Footer"
    Selection.Copy
    Application.Wait Now + TimeSerial(0, 0, 1)
    Sheets("Takeoff").Select
    
Dim myRange As Range
    On Error Resume Next
    Set myRange = Application.InputBox(Prompt:="Select Cell to Paste Clipboard Contents", _
                                       Title:="Format Titles", Type:=8)
    On Error GoTo 0
  If myRange Is Nothing Then
        MsgBox "No selection made", vbCritical, "Input required"
        Exit Sub
    End If
        
    Selection.Insert Shift:=xlDown
    Application.CutCopyMode = False


End Sub

 

[Max1616]

Try this:

Sub a_0_0_Paste_Footer()

Dim myRange As Range
Dim CopyRange As Range

Set CopyRange = Range("_0_a_a_Combo_Box_Footer")


On Error Resume Next
Set myRange = Application.InputBox(Prompt:="Select Cell to Paste Clipboard Contents", Title:="Format Titles", Type:=8)

If myRange Is Nothing Then
    MsgBox "No selection made", vbCritical, "Input required"
    Exit Sub
End If

CopyRange.Copy Destination:=myRange

End Sub

 

[norts55]

Thank you Max1616 for the response. This works very well. However, I realize that I did not describe the last step clear enough. instead of using “paste” where the cells are over written, I need this to insert and shift cells down. Is that possible? The reason I need this is because I have filled in cells below where I am pasting this and I cannot have that information over written.

 

[Max1616]

Give this a try:

Sub a_0_0_Paste_Footer()

Dim myRange As Range
Dim CopyRange As Range

Set CopyRange = Range("_0_a_a_Combo_Box_Footer")


On Error Resume Next
Set myRange = Application.InputBox(Prompt:="Select Cell to Paste Clipboard Contents", Title:="Format Titles", Type:=8)

If myRange Is Nothing Then
    MsgBox "No selection made", vbCritical, "Input required"
    Exit Sub
End If

CopyRange.Copy

myRange.Insert Shift:=xlDown

End Sub

 

[norts55]

That works!!! Thank you very much.