I ran a forum search but kept getting an error message.
I want to copy the rows of information to another sheet named "copy" if the cells in column R have a value (any value, can be number or letter, though it should be a number of 0 or greater). Out of 3000 rows, there will typically only be 8-10 rows that have a value in this column.
Sub Copy_Over()
FinalRow = Cells(Rows.Count, 5).End(xlUp).Row
Application.DisplayAlerts = False
On Error Resume Next
sheets("copy").Delete
Application.DisplayAlerts = True
On Error GoTo 0
sheets.Add.Name = "copy"
sheets("export").Activate
For Each Cell In Range("R2:R" & FinalRow)
If Cell.Value >= 0 Then
Cells.EntireRow.copy Destination:=sheets("copy").Range("E" & FinalRow + 1)
End If
Next Cell
sheets("export").Activate
End Sub
The attached workbook is an example. It has numbers in column R. These are the only rows of information I would like to copy to the sheet "copy". The reason for doing this is to do calculations with these chosen rows without harming the original sheet "export".
The code above results in a seemingly unending loop if I run the macro while Column R is filtered to show only nonblanks. I must hit "esc" to regain control. However, the rows do get copied over to the new sheet "copy".
If I leave column R unfiltered and run the macro, I get:
Run-time error '1004':
To paste your cells from an Excel worksheet into the current worksheet, you must paste into the first cell (A1 or R1C1).
Can someone
1. Educate me on the difference in running this macro with a filtered column R verses and unfiltered one.
2. Show me code that would accomplish my goal.
In my job, I must pick rows every day from a large spreadsheet such as this, except the spreadsheet is much larger than that in my upload. Then I must understand whether or not my choices are cost effective (column R being my choices); hence the reason for getting the rows of data to a different sheet for manipulation. If I make bad choices, I must return to the original "export" and choose again, so I don't want to manipulate the original spreadsheet.
Bookmarks