+ Reply to Thread
Results 1 to 6 of 6

Paste method fails where it used to work just fine.

  1. #1
    Registered User
    Join Date
    04-18-2006
    Location
    Honolulu, HI USA
    Posts
    3

    Question Paste method fails where it used to work just fine.

    Aloha all:

    Did something just change in the Excel environment or in the VBA environment several days ago? I have a data cleaning macro that I've been using for years, all of a sudden it hangs up on a basic Paste line.

    When the macro is launched, it copies some cells on the same row as the active cell, moves up a few rows based on criteria in column B, and pastes the copied cells. The line of code is

    ActiveSheet.Paste Destination:=Cells(ActiveCell.Row, ColNum)

    where ColNum has been set a little earlier in the macro. Now I get a runtime error message, "Paste method of Worksheet class failed" but the data IS ACTUALLY pasted in the right place at that point. Why could it be erroring AFTER pasting? Why is it erroring at all? Again, I have been using this macro for years and was using it for a couple hours on Monday 5/20 (I think) when at about 3 PM HI time it started failing at this line.

    All comments and help are appreciated.

    Mahalo,

    John Jacobson

  2. #2
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Paste method fails where it used to work just fine.

    Why not post your code? It'll be easier for people to help if you do that.
    If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.

    ---Keep on Coding in the Free World---

  3. #3
    Registered User
    Join Date
    04-18-2006
    Location
    Honolulu, HI USA
    Posts
    3

    Re: Paste method fails where it used to work just fine.

    I thought it might confuse more than help to see the full macro. Here it is in all its glory. Very simple really, I can't figure why it's erroring. Would it help to provide an excel sheet with sample data?

    Please Login or Register  to view this content.
    Mahalo again for all help,

    John Jacobson

  4. #4
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Paste method fails where it used to work just fine.

    Have you stepped through the code using F8 to see exactly which line is throwing the error?

  5. #5
    Registered User
    Join Date
    04-18-2006
    Location
    Honolulu, HI USA
    Posts
    3

    Re: Paste method fails where it used to work just fine.

    Yes. It fails on the line

    ActiveSheet.Paste Destination:=Cells(ActiveCell.Row, ColNum)

    whether I'm stepping through or running. I've added watches on ActiveCell.Row and ColNum while stepping through, and those values are as I expect. Again, the macro halts AFTER the data is actually pasted. That strikes me as odd.

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Paste method fails where it used to work just fine.

    The real success for you will come when you've successfully eliminated "SELECTING" from your code to find spots to paste things, or to assess cell content. Humans have to select a cell to look at or act up on it, but VBA does not.

    I've tried to fix you macro so that it no longer guesses at blank cells by selecting them first. This may work, but there was no sample workbook to test on and no verbal explanation of what the macro is accomplishing, so I had to surmise it from the code. If it doesn't work, perhaps you can fix it by DEBUGGING where it fails.

    If you can't please attach a sample workbook we can test with together.

    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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