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

0
votes

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

2
excelvba
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. – Pᴇʜ
Range(Split(Cells(1, j).Address, "$")(1) & 2) is equivalent with Cells(2, j). – AcsErno
@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 – user3670700

2 Answers

1
votes

There are several issues:

  1. 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

  2. Range(Split(Cells(1, j).Address, "$")(1) & 2) is the same as Cells(2, j)

  3. 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
0
votes

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