+ Reply to Thread
Results 1 to 5 of 5

PasteSpecial in VBA Code No Longer Working

  1. #1
    Registered User
    Join Date
    06-27-2012
    Location
    SoCal
    MS-Off Ver
    Excel 2010
    Posts
    3

    PasteSpecial in VBA Code No Longer Working

    So ... this code was working in February ... but now I get the error message "run-time error '1004': PasteSpecial method of Range class failed"

    Instead of pasting the copied cells, it appears to just insert one blank cell (shifting that column down).

    I went through step by step and the macro is selecting and copying the correct cells, and moving back to the correct worksheet and location ... it's just the PasteSpecial that is not working.

    Please Login or Register  to view this content.
    Any suggestions?

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,173

    Re: PasteSpecial in VBA Code No Longer Working

    How would you have us test this and observe the problem?


    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    06-27-2012
    Location
    SoCal
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: PasteSpecial in VBA Code No Longer Working

    I think I am attaching the files below (I did some replacing to make possibly identifying information gibberish).

    The Committees spreadsheet is the main sheet, where the macro is. Google Doc Download is where the info to populate the main sheet comes from. Let me know if there is something else you would need.
    Attached Files Attached Files

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,173

    Re: PasteSpecial in VBA Code No Longer Working

    Can you step through the process and explain where the code fails ... module and line.

    I think I've tried everything and it has all worked. The only exceptions seem to be a message saying "Role1" exists, do you want to use it? And it seems to want to open a backup copy of the Google Downloads workbook.

    The download workbook is very slow to open because there are 600+ rows by 7 columns of Array Formulae which mostly produce #NUM! errors.

    The code could be more efficient as you select sheets, column and cells in order to do stuff ... mostly if not totally unnecessary.

    Regards, TMS

  5. #5
    Registered User
    Join Date
    06-27-2012
    Location
    SoCal
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: PasteSpecial in VBA Code No Longer Working

    Module8. Line 37 (Selection.PasteSpecial Paste:=xlPasteValues).

    Yes, it's clunky. But it was doing what it needed to. Now, it is not (for me, apparently). The "Role1" question used to also come up for me, but I do not get there now.

    When I run through line by line, and paste into a separate Excel document after running line 36, the correct items paste, but I get the warning "The range you are pasting contains formulas that cannot be pasted into this instance of Excel; however, the formulas will be pasted as values. This may result from pasting values from Protected View, another instance of Excel or from another application." When I try going to Paste Special, it indicates that I am trying to paste a "Microsoft Excel Worksheet (code) object" that I can paste as a picture, Bitmap, etc., but not as values. So this seems to be the problem (because my macro is trying to paste values).

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Private sub code no longer working (print area macro)
    By rikkyshh in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-09-2013, 10:43 AM
  2. [SOLVED] Code stopped working, can no longer copy and save a worksheet
    By Oracle in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-30-2012, 07:56 PM
  3. Email Out of Excel Code no longer working due to update or drive change.
    By Kidcarlosuk in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-12-2012, 06:37 AM
  4. VBA Code no longer working
    By jdbel in forum Excel General
    Replies: 16
    Last Post: 11-16-2011, 07:33 PM
  5. mail Code no longer working in 2007
    By lonnied in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-10-2007, 06:14 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1