Error: "PasteSpecial method of Range class failed"

Comment out the On Error line of code.

You have two PasteSpecial lines of code. Determine which one is causing the error. If it is the second one, when the error occurs, go into the debugger and determine the address of wR and the value of colOffset at the point where the error occurs. Make sure that the Offset results in a valid Range reference.

Determine if the destination sheet is protected.

I do not see Wks declared or defined. What is it?

Also your code is almost unreadable. You should indent to show structure.

Also

Sorry for the untidy code and thanks for the help. Here is the entire code including the globally defined variables.
wR is the range in the Data worksheet and the offset is fixed. I am just copying Range C2:C10 and F2:F5 from the source workbooks that I open from Folder and colOffset is 9 as pasting it as transpose to Data worksheet in ThisWorkbook. They are perfectly valid ranges at each iteration.

I am surprised and confused at what is causing this error. Because sometimes it copies all the workbooks fine without a pastespecial error. But when I run it next time I run it shows a pastespecial error with some random workbook and skips copying data from those random workbooks. This error comes very randomly so I am not able to figure out the cause.

I even tried which of the two pastespecial is causing the error by appending an integer to log, but that is also random. It's the first line of pastespecial sometimes and the second pastespecial the other time.

Please Login or Register  to view this content.

Did you try any of the troubleshooting steps I suggested? The best way to diagnose an error is to inspect the state of the data at the point where the error occurs.

Originally Posted by MITH_N

This error comes very randomly

I don't see anything inherently wrong in the code. If the error is not reproducible it would be hard to solve it just by reading the code. I would have to see the error occur.