Can't paste into new worksheet-CodePudding

I have a code where I am trying to paste a selection of rows to a new sheet. It worked previously, but now every time that I try to run it, VBA tells me that I can't paste it because the Copy area and paste area aren't the same size.

As I said, it worked in the past and I have not changed it, but now I get this prompt and run-time error 1004. When I attempt to run the code, I am sure to have the A1 cell of the new sheet selected, so I'm not sure why this is occurring. Does anyone have any thoughts?

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

The code is as follows:

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

CodePudding user response：

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.

CodePudding user response：

"I am sure to have the A1 cell of the new sheet selected" - from your code Range("W1").Select looks like your last selected cell is W1, so you can't paste a whole row there. The solution is to change W1 to A1 or select not the entire row but the range of the table, for example, Worksheets ("ETM ETM0007"). Range ("A" & Startrow & ":" & "I" & Endrow) .copy or any other col/row range copy method.