Results 1 to 3 of 3
  1. #1
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post
    I have these two lines of code:

    ActiveSheet.UsedRange.Copy
    ActiveSheet.PasteSpecial Paste:=xlPasteValues

    The second line errors out with an application or object defined error. It probably doesn't know where to paste it at because the last thing selected is the macro button that executes the vba code.

    I can get it to work if I "select" the usedrange first and then pastespecial since it is now selected but I do not think I should have to select it. Is there a way to tell excel where to paste without selecting it? Like finding the first cell in the usedrange and paste into that?
    You know it's time to diet when you push away from the table and the table moves.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could use

    Code:
    With ActiveSheet.UsedRange
      .Copy
      .PasteSpecial Paste:=xlPasteValues
    End With
    or
    Code:
    With ActiveSheet.UsedRange
      .Value = .Value
    End With

  3. #3
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post
    [quote name='HansV' post='774486' date='09-May-2009 14:42']You could use

    Code:
    With ActiveSheet.UsedRange
      .Copy
      .PasteSpecial Paste:=xlPasteValues
    End With
    or
    Code:
    With ActiveSheet.UsedRange
      .Value = .Value
    End With
    [/quote]

    Both codes worked perfectly. Thanks Hans! I am sure glad you were on the forum this weekend. Your help is very much appreciated!
    You know it's time to diet when you push away from the table and the table moves.

Posting Permissions

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