none
VBA copy-Paste data into a cell RRS feed

  • Question

  • Hello experts,

    I'm almost embarrased to ask this, but I cannot get a set of cells (U18:U24) copied and then pasted on a user-provided location in the spreadsheet. I get an: "Object variable with block variable not set" error with the code below. If I define the collectionCell as String I get a different error:

    '
    ' Collect Macro
    '
    Dim collectionCell As Range
        Range("U18:U24").Select
        Selection.Copy
        collectionCell = Application.InputBox("Which cell do you want to collect the values in?", Type:=8)
        Range(collectionCell).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End Sub

    Any advice on how to fix this?

    Saturday, August 10, 2013 3:24 PM

All replies

  • 1) collectionCell is an object variable, so you have to use the keyword Set.

    2) Using an inputbox of type 8 clears the clipboard so you must first ask the user to specify the range, then copy and paste.

    3) It isn't necessary to select cells in the code.

    Sub MacroName()
     '
     ' Collect Macro
     '
        Dim collectionCell As Range
        Set collectionCell = Application.InputBox("Which cell do you want to collect the values in?", Type:=8)
        Range("U18:U24").Copy
        collectionCell.PasteSpecial Paste:=xlPasteValues
    End Sub


    Regards, Hans Vogelaar

    Saturday, August 10, 2013 4:29 PM
  • This works great! Thank you, Hans.

    Alejandro.

    Saturday, August 10, 2013 5:24 PM