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:
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"