A quick question: runtime error 1004 paste method of worksheet class failed

[Mr.Karr]

Hello

Can anyone pls tel me why I'm getting the below error;

runtime error 1004
Paste method of worksheet class failed

Range("A21").End(xlDown).Select

ActiveSheet.Paste

thanks in advance

 

[sn152]

Hi,

The code shows that you have copied nothing. May be that is why you are getting the error when trying to paste.

 

[Mr.Karr]

@sn151 : no, I want to highlight where I was getting error.
Above shown is, selecting a cell & pasting special

 

[sn152]

Hi
Try this...

Range("A21").Select

ActiveSheet.Paste

 

[Mr.Karr]

Hi, I need to select the last row in column A & paste it

 

[NARAYANK991]

Hi ,

The reason given by sn151 is correct.

When you execute the ActiveSheet.Paste command , Excel assumes that the Clipboard has some content which has already been copied to it.

When it does not find anything , then this error is generated.

To verify this , within your worksheet , select any cell , and press CTRL V ; see if anything is pasted.

If nothing is pasted , then there will be an error when you execute the VBA command to do the same.

If something is pasted , then this error will not be generated.

 

[Mr.Karr]

@NARAYANK991 : it does copy a range and pastes into another sheet if I manually do.

full code"

Range("A3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy



Sheets("Sheet1").Activate

Sheets(Range("AR1").Value).Select 


'Sheets("B & S").Activate


Range("A20").Select
'Range("A65536").End(xlUp).Offset(1, 0).Select
Range("A21").End(xlDown).Select

ActiveSheet.Paste

Another thing is: when I do in de-bug mode, it works pefectly. But not from button click. Probably, below line which helps to select another sheet based on a cell value. Is this causing problem? pls help

Sheets(Range("AR1").Value).Select

 

[sn152]

@Mr.Karr

Try using this to paste in the last row of column A

Range("A1").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste

 

[NARAYANK991]

Hi ,

A lot of unnecessary Selects have been used.

Prior to your first executable line of code , the Activesheet is not known. When you press the button to execute the code , the active sheet is the one on which the button has been placed ; when you run it from the VBE using F8 or F5 , the active sheet can be any other sheet.

It is always good practice to either activate the desired worksheet or qualify all references to the desired worksheet.

Consider the following 2 lines :

Sheets("Sheet1").Activate

Sheets(Range("AR1").Value).Select

The Range("AR1") is on which sheet ?

You might think that because Sheet1 has been activated prior to this statement that it refers to the cell AR1 on Sheet1 ; this is not so. If this code is placed in the section relating to say Sheet3 , then the above reference is to the cell AR1 in Sheet3.

If you want to reference cell AR1 in Sheet1 , the correct way would be to use :

Sheets(Sheets("Sheet1").Range("AR1").Value).Select

or , after you have activated Sheet1 , qualify the range reference with the ActiveSheet keyword , as in :

Sheets(ActiveSheet.Range("AR1").Value).Select

If the code is placed in the Sheet1 section itself , then obviously the Activate statement is not required at all , unless you have activated some other sheet prior to this statement.

Of course , if the code needs to refer to the sheet in whose section it has been placed , the better way would be to use the Me keyword , which will automatically resolve to the correct sheet irrespective of which sheet is the active sheet.

These 2 lines do not do anything other than some unnecessary work :

Range("A20").Select
Range("A21").End(xlDown).Select

The first line can be omitted without any consequence.

If you can explain exactly what AR1 contains , which sheet it refers to , and what you want to do , the same functionality may be achieved with fewer lines of code , and in a more efficient way.

Narayan

 

[Mr.Karr]

thanks Narayan

 

[kylfederer12345]

When I faced the run time error in 1004, then I followed the steps below and it fixed the problem.

1. Create a new workbook, and then delete all of the worksheets except for one.
2. Format the workbook, and then add any text, data, and charts that you must have in the template by default.
3. Click the Microsoft Office Button, and then click Save As.
4. In the File name box, type the name that you want for the Excel template.
5. In the Save as type list, click Template (*.xltx), and then click Save.
6. To insert the template programmatically, use the following code:

Sheets.Add Type:=path\filename

In this code, path\filename is a string that contains the full path and file name for your sheet template.

And you will find that the problem is fixed. For more solutions for the runtime error 1004 in Excel, You can read-- How to fix RUNTIME ERROR 1004?