Paste into new worksheet

Question

I am trying to paste a selection of rows to a new sheet.

It worked previously, but now run-time error 1004 tells me that I can't paste because the copy area and paste area aren't the same size.

When I attempt to run the code, I am sure to have the A1 cell of the new sheet selected.

When I debug, it takes me to the ActiveSheet.Paste line.

Sub exportconditionstarttoend()
    Dim rownum As Long
    Dim colnum As Long
    Dim startrow As Long
    Dim endrow As Long
    Dim lastrow As Long
    rownum = 1
    colnum = 1
    lastrow = Worksheets("ETM ETM0007").Range("W63000").End(xlUp).Row
    
    With ActiveWorkbook.Worksheets("ETM ETM0007").Range("W1:W" & lastrow)
    
        For rownum = 1 To lastrow
            Do
                If .Cells(rownum, 1).Value = "Condition 1 - Price 0.25" Then
                    startrow = rownum
                End If
    
                rownum = rownum + 1
       
                If (rownum > lastrow) Then Exit For

            Loop Until .Cells(rownum, 1).Value = "Condition 1 - Price 0.25 - End"
            endrow = rownum
            rownum = rownum + 1
    
            Worksheets("ETM ETM0007").Range(startrow & ":" & endrow).Copy

            Sheets("Result").Select
            Range("W1").Select
            ActiveSheet.Paste
        Next
    
    End With
   
End Sub

Answer 1

You are getting the error because you are copying every column and then trying to paste every column starting at cell W1. You are trying to paste 16,384 columns into a range that only has 16,362 columns. This leaves you with a 22 column deficit and thus the paste size error.

If you are copying every column, you always need to paste on Column A.
Similarly, if you are copying every row, you always need to paste on Row 1.

i.e. change Range("W1").Select to Range("A1").Select. Note you don't need to .Select a range to copy or paste it - see this post for reasoning and best practice.

---

Do you really need to copy every column?

The best solution is to limit the number of columns you need to copy either by hard coding the column range or dynamically defining the column range.