Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    I've recorded the macro below to Paste-Values into one workbook from another workbook.
    The first three lines are remarked-out since I want the user to define the source range to copy and then switch to the target range in which to paste the values
    When I try to run the macro (after having copied and then switched to the target cell) the macro bombs-out.

    How should this code be modified so that I can just paste the values of whatever (and from wherever) I copied into whatever target cell I choose?

    Sub PasteValues()
    '
    ' PasteValues Macro
    ''

    'rem ActiveWindow.ActivateNext
    'rem Selection.Copy
    'rem ActiveWindow.ActivateNext

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    End Sub

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    When the user switches to the second workbook and clicks on a cell that cell becomes the "Selection". My assumption here is when you say bombs out you mean it just doesn't work. If you are getting an error message we would need to know that but I'm pretty sure the "Selection" is your problem.

    Just to satisify my curosity, why are you trying to do this as it seems a lot more work than just teaching the user to Ctrl+C & Crtl+V since they are doing all the rest of the work.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    There is no error message, it simply does not execute.

    After a user has selected and copied data from the source workbook, the user will switch to a different workbook (the target), select a cell and then run the macro.
    But I don't want the user to simply just Paste. In this case I am trying to import by Copy-PasteValues data created from another application which has generated its own Excel sheet.
    But sometimes these spreadsheets contain data or extra characters or formats that I do not want placed in the target worksheet.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Does this code do what you want?

    Steve

    [codebox]Option Explicit
    Sub CopyPasteValues()
    Dim rSource As Range
    Dim rDest As Range

    On Error Resume Next
    Set rSource = Application.InputBox _
    (Prompt:="Please select the Range to copy FROM", _
    Title:="Copy FROM", Default:=Selection.Address, Type:=8)
    On Error GoTo 0

    If rSource Is Nothing Then
    MsgBox "No Source range was selected"
    Exit Sub
    End If

    On Error Resume Next
    Set rDest = Application.InputBox _
    (Prompt:="Please select the Range to copy TO" & vbCrLf & _
    "NOTE: only Upper left cell is required", _
    Title:="Copy TO", Type:=8)
    On Error GoTo 0

    If rDest Is Nothing Then
    MsgBox "No Destination range was selected"
    Exit Sub
    End If

    rSource.Copy
    rDest.PasteSpecial (xlPasteValues)
    End Sub[/codebox]


  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    When you say simply does not execute, do you mean that


    the code does not RUN at all
    the code does NOT paste
    the code errors

    Excel is a bit quirky with how long it retains details in the clipboard.
    It depends on the actions the user takes between the copy and the later paste
    Certain actions will often clear the clipboard, like entering data into a cell.
    There is probably a list somewhere, but I am not sure what it is.

    Have you checked to see if the code actual runs by putting a break point in the macro?

    Andrew

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Steve & Andrew,

    Thanks for your speedy replies. Unfortunately, my boss just gave me a new project due this afternoon. So I'll have to sign off and switch gears.

    Steve,
    I'll run your code and see. I want the user to stop between steps anyway so I'm sure your code will work just so long as one can switch between workbooks.


    Thanks lots and have a great weekend.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •