PasteSpecial twice crashes Excel VBA

Question

I've been looking for a solution to using .PasteSpecial more than once in a an excel vba macro. I have reports that can sometimes have thousands of rows. In these reports are two columns, I need to merge these columns so that if one column has blanks, I want the value from the second column; otherwise, just keep the value in the first column. I need to apply this twice in two different places. This is all tucked into a larger code.

My solution is to utilize .PasteSpecial with "Skip Blanks". It is quick for excel to process, much faster than looping row by row. The problem is that the code keeps crashing excel.

After debugging, here is what I've learned so far: *The first .PasteSpecial always works, but when it gets to the second .PasteSpecial it always fails. *I've tried STOP after the first .PasteSpecial then step through the code, and after I step through the second.PasteSpecial the code works just fine. *If I step through the second .PasteSpecial it works like nothing is wrong - but if I just run the code like normal it crashes. *I switched the order of the two .PasteSpecials within the code. When I do this, it no longer crashes on the problematic .PasteSpecial, but it does crash on the originally working .PasteSpecial.

Based on this, I know the problem is Excel doesn't like .PasteSpecial twice in a code. Still cannot find a work around. I've tried emptying the clip board, and I don't know enough how to set up an array let alone if that is efficient for this much data. Anybody know of a solution or work around?

Here is my .PasteSpecial code:

MainSheet.Range("N:N").Copy
MainSheet.Range("P:P").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=True, Transpose:=False

MainSheet.Range("R:R").Copy
MainSheet.Range("Q:Q").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=True, Transpose:=False

Answer 1

EDIT:

A better answer that worked more reliably than my original found solution is below. This is an adaptation of some advice from FaneDuru's answer. This solution is more taxing on resources; however, for now - it performs the task reliably (without crashing). I would love for there to be a better answers than row looping; however, this does answer my OP. Thank you for all the help!

    Sub copyColumnsArray()
 Dim lastR As Long, arrCopy
 
 lastR = MainSheet.Range("N" & rows.count).End(xlUp).row
 arrCopy = MainSheet.Range("N1:N" & lastR).value 

Dim ArrayIndex as Variant
Dim RowCount as String
RowCount = 1

For Each ArrayIndex in arrCopy
  If ArrayIndex = "" then
    RowCount = RowCount +1
    'Skip Blank
   else
    MainSheet.Range("P"+RowCount).value = ArrayIndex
    RowCount = RowCount + 1
   end if
 Next
 

 lastR = MainSheet.Range("R" & rows.count).End(xlUp).row
 arrCopy = MainSheet.Range("R1:R" & lastR).value

RowCount = 1

For Each ArrayIndex in arrCopy
  If ArrayIndex = "" then
    RowCount = RowCount +1
    'Skip Blank
   else
    MainSheet.Range("Q"+RowCount).value = ArrayIndex
    RowCount = RowCount + 1
   end if
 Next

End Sub