Copy cells selected by user and paste to location that they select (VBA Excel 2003)

[DeusXv]

I am trying to make a macro that allows a user to select a cell or a range of cells and then paste them to a location that they want.

I found some code that allows the user to select a range to paste to and tried to modify it to also copy but to no avail, can anyone help me out.

Here is the code that pastes to a range that you select:

Sub PasteRange()

    With Sheets("Sheet1")
        On Error Resume Next
        Set Ret = Application.InputBox(Prompt:="Please select a range where you want to paste", Type:=8)
        On Error GoTo 0


        If Not Ret Is Nothing Then
            .Range("$AD$10").Copy


            Ret.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        End If
    End With
    
End Sub

How can i edit the above so that it copies a selected range.

I tried this but it didnt work

    With Sheets("Sheet1")
        On Error Resume Next
        Set Ret = Application.InputBox(Prompt:="Please select a range to copy", Type:=8)
        On Error GoTo 0


        If Not Ret Is Nothing Then
            .Range("$AD$10").Copy


            Ret.CopySpecial Copy:=xCopyValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        End If
    End With

Thanks in advance

- DeusXv

 

[Andrew Poulsom]

You haven't prompted the user for the paste range and there is no such method as CopySpecial.

 

[DeusXv]

You haven't prompted the user for the paste range and there is no such method as CopySpecial.

Yeah I thought as much about the copyspecial, is there another way around this like I want to user to be able to select a range of cells to copy ?

And when I run this code:

    With Sheets("Sheet1")
        On Error Resume Next
        Set Ret = Application.InputBox(Prompt:="Please select a range where you want to paste", Type:=8)
        On Error GoTo 0


        If Not Ret Is Nothing Then
            .Range("$AD$10").Copy


            Ret.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        End If
    End With

The user gets to select a range, but even if I have Ctrl+C some cells and then run it, it still doesn't copy it to the selected cell range.

Could you help me out with this as I don't really understand what the above code is doing, I just took it from a thread I saw online.

Thanks In advance

 

[DeusXv]

i was wondering can I just get the user to highlight the cells and then run the macro like this

Sub YesMacro()


Selection.Copy


    With Sheets("Sheet1")
        On Error Resume Next
        Set Ret = Application.InputBox(Prompt:="Please select a range where you want to paste", Type:=8)
        On Error GoTo 0


        If Not Ret Is Nothing Then
            .Range("$AD$10").Copy


            Ret.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        End If
    End With
    
End Sub

But how can I get the cells to actually paste into the cells that the user selects. Am I missing something like a Selection.Paste ?

 

[Andrew Poulsom]

Does this work for you?

Sub YesMacro()
    On Error Resume Next
    Set Ret = Application.InputBox(Prompt:="Please select a range where you want to paste", Type:=8)
    On Error GoTo 0
    If Not Ret Is Nothing Then
        Selection.Copy
        Ret.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
    End If
End Sub

 

[DeusXv]

Yeah that works perfectly, I also found these lines of code which allows the user to select a range and copy it, I changed it so they can select a range (by dragging the mouse across the screen) but it wont paste it and says "Global Objective failed"

Here is the code

Dim clAddress As String
Dim cl As Range
Dim rng As Range


Set rng = Range(Selection.Address)
clAddress = Application.InputBox("Enter the address of the cell you want to copy", "Input Range", "A1")
Set cl = Range(clAddress)
cl.Copy
ActiveSheet.Paste
    
End Sub

It says the error is from the line in red, what do I need to change that too in order for it to run perfectly.

What I am hoping to do is try add what you gave me and this together. I don't know if it is possible but I said it was worth a try anyway

 

[Andrew Poulsom]

clAddress is empty if you select a range. Excel thinks that you are entering a formula. You need Type:=8 to for a range and you need to use the Set keyword to assign it to a variable.