I'm trying to make a macro that will select the even number of rows and paste into a new workbook.
-------------------------------------------
Here is my code:
Sub rows()
Dim s, collection As String
collection = "2:2"
endrow = 44
'errors when I put anything above 44
For x = 2 To endrow
s = 2 * x & ":" & 2 * x
collection = collection & "," & s
Range(collection).Copy
Next x
End Sub
------------------------
The code stops working when I change endrow from 44 to 45. There appears to be a limit to the number of rows Excel VBA wants to copy or select. The only other way I can think of is making a loop that selects one row and immediately pastes it into the new workbook and repeats. Is there any other way that it can select all the rows that fit the criteria and pastes it over in one block?
Any help would be appreciated!
-------------------------------------------
Here is my code:
Sub rows()
Dim s, collection As String
collection = "2:2"
endrow = 44
'errors when I put anything above 44
For x = 2 To endrow
s = 2 * x & ":" & 2 * x
collection = collection & "," & s
Range(collection).Copy
Next x
End Sub
------------------------
The code stops working when I change endrow from 44 to 45. There appears to be a limit to the number of rows Excel VBA wants to copy or select. The only other way I can think of is making a loop that selects one row and immediately pastes it into the new workbook and repeats. Is there any other way that it can select all the rows that fit the criteria and pastes it over in one block?
Any help would be appreciated!