Paste method of worksheet class failed

  • Good day

    I'm having problem with pasting a range of cells, from one worksheet to another. It has worked 100 times before, but since stopped working. I get the error: "Paste method of worksheet class failed". I've been upgrading many parts of the program, which I didn't think would be of concern. It fails on all 9 sheets that it's supposed to update, so I don't think that it's caused by the destination. I stepped through it and watched the 'Clipboard' and it is copying the range to it, so the copy seems fine. I have no idea at which point it failed, as I hadn't used this sub for a while. If you can just give me some advice on what to check next, I'd appreciate it greatly.

    Here's part of the sub that the code is failing to paste (ActiveSheet.Paste). Ignore the rem statements (comments)...trying different things:

    Thanks JimmyB

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Hi Dave

    Just so you know, this code works well at about 50 other locations in my program.

    Code
    Sub Column1_FirstBlank()
        Dim ws As Worksheet
        Set ws = ActiveSheet
        For Each cell In ws.Columns(1).Cells
            If IsEmpty(cell) = True Then cell.Select: Exit For
        Next cell
    End Sub

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Replace the copy and paste part with this and see what happens. No need for the "Call Column1_FirstBlank" with this.

    Code
    Sheets("Default Sheet").Range("A55:L55").Copy Sheets("VALUES for MEALS").Cells(Rows.Count, 1).End(xlUp).Offset(1)
  • Tried it on a small sample and it works as advertised.

    No macro will work if you don't fix the following.

    Do you have 2 sheets named the same except capitalization?

    Copied from your code in the first post:

    Code
    Sheets("VALUES for MEALS").Activate
    Code
    ActiveWorkbook.Worksheets("VALUES FOR MEALS").Range("A4:K" & LastRow).Select
  • If you stick to proper capitalization at all times you wont run into the problem mentioned previously.

    Note that I changed your sheet names.


    All three work as advertised.

    Code
    Sub Maybe_2()
    Sheets("Values For Meals").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(,12).Value = Sheets("Default Sheet").Range("A55:L55")
    End Sub


    Code
    Sub Maybe_3()
    Dim sh1 As Worksheet, sh2 As Worksheet, nr As Long
    Set sh1 = Sheets("Default Sheet")
    Set sh2 = Sheets("Values For Meals")    '<----- Note capitalization
    nr = sh2.Cells(Rows.Count, 1).End(xlUp).Offset(1).Row    '<---- Replaces your "Column1_FirstBlank" macro
    sh1.Range("A55:L55").Copy sh2.Cells(nr, 1)
    End Sub



    Code
    Sub Maybe_4()
    Dim sh1 As Worksheet, sh2 As Worksheet, nr As Long
    Set sh1 = Sheets("Default Sheet")
    Set sh2 = Sheets("Values For Meals")    '<----- Note capitalization
    nr = sh2.Cells(Rows.Count, 1).End(xlUp).Offset(1).Row    '<---- Replaces your "Column1_FirstBlank" macro
    sh2.Cells(nr, 1).Resize(, 12) = sh1.Range("A55:L55").Value
    End Sub
  • Similar to previous post.


    Set your sheets

    set your range to copy

    find the last row

    get the value of the range to copy

    Sort


  • Ok I want to make sure that I respond to all of you people that helped me with my issue.

    Hi Maqbool...no change. I also tried .MealItem.paste with your 'with' statement. Thanks.

    Hi Jolivanes. Re: post #7. What I need to do is just copy the range A55:L55 (MealItem) from 'Default Sheeet' to the bottom of a list in 'VALUE for MEALS' sheet. The sub then sorts it alphabetically to Column 'A'. Following this the range 'MealItem' is transferred to 9 more sheets which are then sorted to various columns, depending on certain criteria. This worked for me to insert over 150 rows of items. It's faulting out on the line 'ActiveSheet.Paste'. Not sure what else you want here.

    Re Post #8. OOPS my fault...typo...I an't believe that I missed that. No, there is only one sheet 'VALUES for MEALS'. I changed the errors, and there is no difference...but it did work up until recently.

    Re Post #4. The only difference I could see with what I was using, is you are finding the from the bottom and I using from the top. Would it be better practice to use your method when possible...going forward? (still learning)

    Re Post #9. BINGO! got it back. Works great

    Hi Dave. I was going to try your suggestion next. It looks like that would have worked fine. Thanks for that.


    Ok, I just want to thank everyone for all of the input. This section of the program is extremely important an can't go without it. If you want to see the program run just click my YouTube channel. I have videos of me running the program. Videos need updating, as I've made a ton of improvements (like screen updating for one) since. Thanks Again

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Good to hear you have it under control now. Thanks for letting us know.

    For future possible use.

    Getting the last used cell in Rows and Columns (lr = Rows, lc = Columns). Use offset(1) or offset(, 1) for the empty cell

  • Thanks Jolivanes. I'll keep for future considerations.

    Can you explain what I'd be finding with the "*", , , , in the examples above?

    JimmyB

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!