How to fix pastespecial error in Excel VBA : Run time error 1004

Question

I am writing a code to copy paste data from one workbook to another workbook

I am getting an error "PasteSpecial method of Range class failed " op on clicking debug and F5 the code runs without any issues

And I have used Application.displayalerts = false,but still I am getting the error and code runs if I try to press F5

        S_xlobj.Activate
        S_wsObj.Select
        S_wsObj.Range(ThisWorkbook.Sheets("Config").Cells(i, 3) & F1_startRow & ":" & ThisWorkbook.Sheets("Config").Cells(i, 3) & F1_Lastrow).Copy
        D_xlobj.Activate
        D_wsObj.Select
        D_wsObj.Range(Split(Cells(1, j).Address, "$")(1) & 2).PasteSpecial xlPasteValues

I expect it should run without errors. Sometimes it runs without any error but some times I am getting this error

First of all hiding alerts doesn't solve errors! Secondly avoid using `Select` and specify a worksheet for every `Cells` object! Excel cannot know in which sheet `Cells(1, j).Address` is if you don't specify one. Also please tell what the result of `Debug.Print Cells(1, j).Address` is *after* you specified it's worksheet. • Read and apply [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Jul 11 '19 at 11:33
`Range(Split(Cells(1, j).Address, "$")(1) & 2)` is equivalent with `Cells(2, j)`. — AcsErno, Jul 11 '19 at 11:46
@Peh I have updated the code based on your comments but it ran without errors for the first time and again it started giving errors.And Debug.print Cells(1,j).address is giving a valid address to me — , Jul 12 '19 at 06:13

score 1 · Answer 1 · answered Jul 12 '19 at 09:50

There are several issues:

Excel cannot know in which sheet Cells(1, j).Address is. Always specify a sheet!

ThisWorkbook.Worksheets("SheetNAme").Cells(1, j).Address
'or
D_wsObj.Cells(1, j).Address

Range(Split(Cells(1, j).Address, "$")(1) & 2) is the same as Cells(2, j)
Don't use .Select or .Activate it is not needed. How to avoid using Select in Excel VBA.

So this should work (the rest is not needed):

S_wsObj.Range(S_wsObj.Cells(i, 3) & F1_startRow & ":" & S_wsObj.Cells(i, 3) & F1_Lastrow).Copy
D_wsObj.Cells(2, j).PasteSpecial xlPasteValues

score 0 · Answer 2 · edited Jul 20 '19 at 16:39

I think repeating the same action multiple times I am getting this error

Rather than using copy/paste, I have used arrays and now it is working without any issues

New code that I am using (for easy reference: removed variables that are dynamically getting column name)

Dim myArray() As Variant
myArray = S_wsObj.Range("B6:B600").Value  '''storing the values in array rather than copy
D_wsObj.Range("A2:A560") = myArray    ''' Filling range with the array instead of paste

This is working like charm without any issues

Thanks for all your support

How to fix pastespecial error in Excel VBA : Run time error 1004

2 Answers2