Paste method of worksheet class failed

JimmyB · Feb 15th 2020

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

Code

  Sub AutoSortRange()
    
        Application.ScreenUpdating = False
Dim LastRow As Long
Dim MealItem As Range
    
        ActiveWorkbook.Worksheets("Default Sheet").Activate
        Set MealItem = Application.ActiveWorkbook.Worksheets("Default Sheet").Range("A55:L55")
    With MealItem
        .Copy
    End With

    'Sheets("Default Sheet").Activate
     '   Set MealItem = Range("A55:L55")
      '  MealItem.Font.Size = 14
       ' MealItem.Copy
        'This copies the data, that the patient inputted for new meal item to the clipboard

            '***Values for Meals***
    Sheets("VALUES for MEALS").Activate
        Call Column1_FirstBlank
    
    With ActiveCell
     '>>>>>>>>>>>  Failing to paste NEXT LINE  <<<<<<<<<<<<
        ActiveSheet.Paste
    End With
    
        LastRow = Cells(Rows.Count, 11).End(xlUp).Row
        Range("A4:K" & LastRow).Sort Key1:=Range("A4:A" & LastRow), Order1:=xlAscending, Header:=xlNo
        ActiveWorkbook.Worksheets("VALUES FOR MEALS").Range("A4:K" & LastRow).Select
        Application.CutCopyMode = False
        ActiveWorkbook.Worksheets("VALUES FOR MEALS").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("VALUES FOR MEALS").Sort.SortFields.Add Key:=Range( _
        "A4:A" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
   With ActiveWorkbook.Worksheets("VALUES FOR MEALS").Sort
        .SetRange Range("A4:K" & LastRow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
        'This sets the sorting range as well as the Sorting Key to column "A"

Display More

davesexcel · Feb 16th 2020

Quote from JimmyB

Call Column1_FirstBlank

What is the code for this macro?

JimmyB · Feb 16th 2020

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

jolivanes · Feb 16th 2020

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)

JimmyB · Feb 16th 2020

Hi Jolivanes

I tried this and get error: Object doesn't support this property or method

On the line Sheets("Values for.....

Maqbool · Feb 17th 2020

Hi

I think the problem is here:

Quote from JimmyB

With ActiveCell
'>>>>>>>>>>> Failing to paste NEXT LINE <<<<<<<<<<<<
ActiveSheet.Paste
End With

Can you try replacing it with

Code

with ActiveSheet
 .activecell.paste

end with

Regards

Maqbool

jolivanes · Feb 17th 2020

Re: Your post #5.

Explain in a concise manner what you want to achieve before the sorting and show us your code that failed where you tried with what I suggested.

jolivanes · Feb 17th 2020

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

jolivanes · Feb 17th 2020

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

davesexcel · Feb 17th 2020

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

Code

Sub AutoSortRange()
 
    Dim LastRow As Long
    Dim MealItem As Range
    Dim sh As Worksheet, ws As Worksheet
 
    Set sh = Sheets("Default Sheet")
    Set MealItem = sh.Range("A55:L55")
    Set ws = Sheets("VALUES for MEALS")
    
    With ws
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Range("A" & LastRow & ":L" & LastRow).Value _
        = MealItem.Value
        Range("A4:K" & LastRow).Sort _
        Key1:=Range("A4:A" & LastRow), Order1:=xlAscending, Header:=xlNo
    End With
    
End Sub

Display More

JimmyB · Feb 17th 2020

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

jolivanes · Feb 18th 2020

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

Code

lr = Cells(Rows.Count, 1).End(xlUp).Row
lr = Cells(Rows.Count, "A").End(xlUp).Row
lr = Range("A" & Rows.Count).End(xlUp).Row
lr = Cells.Find("*", , , , xlByRows, xlPrevious).Row
lr = Cells.Find("*", ,xlValues , , xlByRows, xlPrevious).Row    '<---- Values only
lr = Cells.Find("*", ,xlFormulas , , xlByRows, xlPrevious).Row      '<---- Includes Formulas


lc = Cells(1, Columns.Count).End(xlToLeft).Column
lc = Range("XFD1").End(xlToLeft).Column
lc = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
lc = Rows("5:10").Find("*", , , , 2, 2).Address (Last Used Column In Rows 5 to 10)

Display More

JimmyB · Feb 18th 2020

Thanks Jolivanes. I'll keep for future considerations.

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

JimmyB

jolivanes · Feb 18th 2020

The asterisk is the wildcard character for any amount of characters.

JimmyB · Feb 18th 2020

Thanks...and the 4 comas?

Sorry to keep bugging you

jolivanes · Feb 19th 2020

If you scroll down a little when on this site, it explains it in an understandable manner.

https://excelmacromastery.com/excel-vba-find/

JimmyB · Feb 19th 2020

Looks like a great site...Thanks again

Paste method of worksheet class failed

Participate now!

Share