0

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
Community
  • 1
  • 1
colclar
  • 3
  • 3

2 Answers2

0

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.

urdearboy
  • 14,439
  • 5
  • 28
  • 58
  • Unfortunately I do need to copy every column. I am away from the computer at the moment, but just to be clear — changing W1 to A1 will not disrupt the fact that the conditions I am extracting are based in column W? For example in this case I am extracting all rows including and between cells named Condition 1 – Price 0.25 and Condition 1 – Price 0.25 End, which are located in column W – colclar Jul 25 '22 at 21:23
  • You have 16K columns and are working in excel? yikes – urdearboy Jul 25 '22 at 21:37
  • You can apply your conditional tests on any column[s] you'd like. But if you are copying every column, you have to paste in Column A. You can prob greatly simplify your macro - are you sure you can't just use `Range.Filter` and then copy the resultant range? – urdearboy Jul 25 '22 at 21:39
0

"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.

Arno Z
  • 3
  • 2