VBA You can't paste this here because the Copy and paste area aren't the same size

the following code is supposed to (amongst other things) select non-adjacent columns in a worksheet, select all the rows with data, copy them to another worksheet and paste. Unfortunately, I am getting the 'You can't paste this here because the Copy and paste area aren't the same size..' message:

Sub Macro2()
'
' Macro2 Macro
 
    Sheets("Share Registry Transactions").Select
    Range("A1:AA2").EntireRow.Select
    Selection.UnMerge
    Rows("1:2").Select
    Selection.Delete Shift:=xlUp
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "=WORKDAY(RC[-1],3)"
    Range("D1").Select
    Selection.Copy
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2:A4,C2").Select
    Range("C2").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2:A4,C2:C4,D2").Select
    Range("D2").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2:A4,C2:C4,D2:D4,E2").Select
    Range("E2").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2:A4,C2:C4,D2:D4,E2:E4,G2").Select
    Range("G2").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2:A4,C2:C4,D2:D4,E2:E4,G2:G4,H2").Select
    Range("H2").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2:A4,C2:C4,D2:D4,E2:E4,G2:G4,H2:H4,I2").Select
    Range("I2").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2:A4,C2:C4,D2:D4,E2:E4,G2:G4,H2:H4,I2:I4,J2").Select
    Range("J2").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2:A4,C2:C4,D2:D4,E2:E4,G2:G4,H2:H4,I2:I4,J2:J4,L2").Select
    Range("L2").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2:A4,C2:C4,D2:D4,E2:E4,G2:G4,H2:H4,I2:I4,J2:J4,L2:L4,M2").Select
    Range("M2").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Daily Recon").Select
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveSheet.Paste
End Sub

Any help greatly appreciated.

CodePudding user response：

So, as already commented this code would be easier to walk through without the selects and activates (though I assume you're fairly new to this so welcome!). also stopping this code at every line shows it doing kind of odd things honestly, at each line you select a number of ranges rows 2 to 4 and one in just row 2 (i.e. A2:A4, C2:C4, D2). then the selection xlDown selects column A row 2 to the end of table (i.e A2:A8 for example), then the code repeats with another range added for selection for rows 2 to 4 but still goes back to selecting column A it appears.

I believe what you want is:

Sub test()

    Set ws = ActiveWorkbook.Sheets("Share Registry Transactions")
    ws.Range("1:2").EntireRow.UnMerge
    ws.Rows("1:2").Delete Shift:=xlUp
    
    lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    
    ws.Range("A2:A" & lastRow & ",C2:C" & lastRow & ",D2:D" & lastRow & ",E2:E" & lastRow & ",G2:G" & lastRow & ",H2:H" & lastRow & ",I2:I" & lastRow & ",J2:J" & lastRow & ",L2:L" & lastRow & ",M2:M" & lastRow).Copy
        
    'Sheets("Daily Recon").Select
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveSheet.Paste
End Sub

this will select columns a,c,d,e,g,h,i,j,l and m from row 2 to the lastrow populated in column A (note: if any other column is populated past the last row in column A those values will be lost. These values would be overwritten in the next running of the code anyway because of how the last three lines assign where to paste the incoming data in sheet "Daily Recon").

It will also put them where you want them using your last bit of code (there is a little inefficiency here from using select. But honestly this code should run relatively quickly so unless you're planning to loop it multiple times I wouldnt worry.