Run Time error '1004': >> PasteSpecial method of Range Class failed.

gg

Well-known Member
Joined
Nov 18, 2003
Messages
560
This file has been running just fine....

But now I got this

Run Time error '1004':
PasteSpecial method of Range Class failed.

any suggestions

Rich (BB code):
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AV2").Select
    Application.CutCopyMode = False
    Range("A1:AV1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=48, Criteria1:="DELETED"
    LstRw = Cells(Rows.Count, "A").End(xlUp).Row
    Range(Cells(2, 1), Cells(LstRw, 48)).Copy
    Sheets("Deleted Records").Select
    LstRw = Cells(Rows.Count, "A").End(xlUp).Row
    Range(Cells(LstRw + 1, 1), Cells(LstRw + 1, 48)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Sheets("SKPI 2008").Select
    Application.CutCopyMode = False
    Range("A1").Select
    Selection.AutoFilter
    Application.Goto Reference:="R1C1:R3500C48"
    Range("A1").Select
    Application.Goto Reference:="R1C1:R3500C48"
    Selection.Sort Key1:=Range("AV2"), Order1:=xlDescending, Header:=xlGuess _
        , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("A1:AV1").Select
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
ignore...see Erik's post -- though you could check that LstRw is not last row in sheet (65536 pending version) -- the +1 would cause failure.
 
Last edited:
Upvote 0
forgot to add a hint
Code:
    With Sheets("Deleted Records")
    LstRw = [SIZE=4][COLOR=red].[/COLOR][/SIZE]Cells([SIZE=4][COLOR=red].[/COLOR][/SIZE]Rows.Count, "A").End(xlUp).Row
    [SIZE=4][COLOR=red].[/COLOR][/SIZE]Range([SIZE=4][COLOR=red].[/COLOR][/SIZE]Cells(LstRw + 1, 1), [SIZE=4][COLOR=red].[/COLOR][/SIZE]Cells(LstRw + 1, 48)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With

watch out for all DOTS
(the only one who you could skip is the one before "Rows.Count", but I presume that once upon at time worksheets in the same workbook will have different amount of rows)
 
Upvote 0
Thanks for the assistance.

I added the .dots and now have a compile error? Invalid or unqualified reference.

One question:
Why would I get this error now? I have been using this file for over 1 year and never seen this error?

lasw - I am not sure what you are saying. row that is highlighted is the correct one. It is 1 row below the last row.
 
Upvote 0
let me guess
this code is in a worksheetmodule
I made an assumption, but you didn't provide feedback

so please tell us
where is that code resided: sheet module or normal module, workbook module or userform module?
how do you run that code: button, event, straight from VBA?
if relevant, tell us a little more about the workbook (what sheets ...)
what sheet is active when the code starts?
is it meant to run in the workbook itself or another one?
or is it an addin?

you see: not clear yet

anyway, I was able to run it

Hmm, when I think more about it: are there any events in the sheet module "Deleted Records"?
they could stop the "copy" when selecting the sheet ... if you see what I mean ... it's always safer to make a straight-one-liner-copy-paste
 
Upvote 0
Sorry I was in a hurry. It is kind of hard for you to help me unless I help you first.

All of my VBA in this excel file are stored as a normal module in the workbook.

I have a module that actually calls other modules because of size problems.
This module can be called from the VBA list but normally I use a button.

There are no events in the sheet mod
 
Upvote 0
there is surely a simple reason for this
you didn't answer
what sheet is active when the code starts?
is it meant to run in the workbook itself or another one?

am I too tired to be able to reproduce the error?
it still runs fine for me

If you do not find the problem, feel free to email me the workbook.
clear all sensitive data and remove worksheets which are not unnecessary for the test
 
Upvote 0
It is most definately meant to run in this workbook only.

Yes the correct worksheet is active when the script is running. In fact the correct row (1 row below the last row of data) is actually highlighted.

Is it possible to get this error if the clipboard is empty?

Even though it has been working all along while being empty?
 
Upvote 0
I don't mind sending you the file. However, this file is very sensitive to various machines. I am currently trying to share the file and getting another error prior to this one.

That problem may be causing me issues here on this one? ( I am not sure?)

Please advise.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top