This macro sends rows from Sheet1 to Sheet2, when it finds an "A" in
the firt cell of the row in Sheet1, sends it to Sheet2. But when there
is no more "A" in the other rows it sends the rows even they dont have
an "A" in their first cell. When it ends all the rows with data and the
Sheet is empty, an error window appears to debug the macro. I do not
know what to do. The macro must take only the rows with "A" in the firs
cell from the left side and move them to Sheet2. If there are not rows
that have "A", then it has to stop moving rows to the other Sheet.

Thanks for your help.

Sub Traspasa_datos()
Application.ScreenUpdating = False
With Worksheets("Sheet1")
If .AutoFilterMode Then .Cells.AutoFilter
.[a1].AutoFilter Field:=1, Criteria1:="A"
With .AutoFilter.Range
With .Offset(1).Resize(.Rows.Count - 1)
.Copy Worksheets("Sheet2").[a65536].End(xlUp).Offset(1)
.EntireRow.Delete
End With
End With
.[a1].AutoFilter
.UsedRange
End With
End Sub

This line works with the data in the autofilter range:

With .Offset(1).Resize(.Rows.Count - 1)

First, it copies it. Then it deletes it.

I bet you want just the visible rows:

With .Offset(1).Resize(.Rows.Count - 1).cells.specialcells(xlcelltypevisible)

======
You may want to check if there's always a visible row.

if .autofilter.range.columns(1).cells.specialcells(xl celltypevisible).count _
= 1 then
'only the header row--no details
else
'do all that copy|delete
end if

Untested, uncompiled. Watch out for typos.


Maxwell wrote:

This macro sends rows from Sheet1 to Sheet2, when it finds an "A" in
the firt cell of the row in Sheet1, sends it to Sheet2. But when there
is no more "A" in the other rows it sends the rows even they dont have
an "A" in their first cell. When it ends all the rows with data and the
Sheet is empty, an error window appears to debug the macro. I do not
know what to do. The macro must take only the rows with "A" in the firs
cell from the left side and move them to Sheet2. If there are not rows
that have "A", then it has to stop moving rows to the other Sheet.

Thanks for your help.

Sub Traspasa_datos()
Application.ScreenUpdating = False
With Worksheets("Sheet1")
If .AutoFilterMode Then .Cells.AutoFilter
.[a1].AutoFilter Field:=1, Criteria1:="A"
With .AutoFilter.Range
With .Offset(1).Resize(.Rows.Count - 1)
.Copy Worksheets("Sheet2").[a65536].End(xlUp).Offset(1)
.EntireRow.Delete
End With
End With
.[a1].AutoFilter
.UsedRange
End With
End Sub

--

Dave Peterson