locked
I want to perform paste special in multiple cells after using Vlookup RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Dear Sir / Madam,

    I have Excel 2013. I have prepared a table in which I used Vlookup in some of the cells. For these cells only, in which Vlookup is applied, I am trying to paste values by applying function paste special.

    I used following sequence:

    1. Selected multiple cells

    2. Control G - Special- Visible cells only

    3. Control C - Paste special - Values

    Following error appears:

    The command can not be used on multiple selections

    Please guide to perform the operation.

    Sunday, July 1, 2018 1:01 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    You'd have to apply Copy / Paste Values to each separate area. You could use a macro for this purpose:

    Sub RemoveFormulas()
        Dim rng As Range
        Dim are As Range
        On Error Resume Next
        Set rng = Selection.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not rng Is Nothing Then
            For Each are In rng.Areas
                are.Value = are.Value
            Next are
        End If
    End Sub

    Warning: make sure that you select the correct range before running the macro.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, July 1, 2018 2:36 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi, 

    Just checking in to see if the information of Hans Vogelaar was helpful. Please let us know if you would like further assistance.

    Regards,

    Emi

    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Monday, July 2, 2018 7:35 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    I am just writing to see if you have obtained the opportunity to test the solution. If anything is unclear with the previous information I've provided to you, please don't hesitate to let me know. I am glad to be of assistance. 

    Regards,

    Emi

    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Wednesday, July 18, 2018 1:32 AM